Results 1 to 3 of 3

Thread: Macro to copy from sheet 1 to sheet2

  1. #1

    Macro to copy from sheet 1 to sheet2



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Members,

    Iam new to VBA and i need help to create a macro.
    The below information is in sheet 1. The solution should be put in sheet 2.
    There are four columns, ProductId, Country(Argentina,France,Italy.....),Cost,and Desc
    If the product Id and Argentina are there i need to copy the rows below and paste in
    another sheet. Please help me to do this.

    Product Id Argentina Cost Desc
    21456 3 $45,000 NF Bill
    23456 45 $65,000
    21345 5 $54,000 NF Bill
    ProductId Turkey Cost Desc
    45678 45 $23,000
    76543 6 $56,000 NF Bill
    67543 78 $35,000
    54376 88 $43,000
    ProductID Argentina Cost Desc
    546345 66 $32,000
    87654 66 $32,000 NF Bill
    45376 66 $23,000
    ProductID France Cost Desc
    59765 45 $32,000
    45376 55 $32,000
    ProductId Argentina Cost Desc
    56423 32 $23,000 NF Bill
    65473 3 $34,000



    Solution:



    Product Id Argentina Cost Desc
    21456 3 $45,000 NF Bill
    23456 45 $65,000
    21345 5 $54,000 NF Bill
    ProductID Argentina Cost Desc
    546345 66 $32,000
    87654 66 $32,000 NF Bill
    45376 66 $23,000
    ProductId Argentina Cost Desc
    56423 32 $23,000 NF Bill
    65473 3 $34,000


    Thanks in Advance.

  2. #2
    Code:
    Public Sub BasicLoop()Dim rowEnd As Long
    Dim rowLast As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With Worksheets("Sheet2")
        
            Worksheets("Sheet1").UsedRange.Copy .Range("A1")
        
            rowLast = .Cells(.Rows.Count, "A").End(xlUp).Row
            rowEnd = rowLast
            For i = rowLast To 1 Step -1
            
                If LCase(.Cells(i, "A").Value) Like "product*id" Then
                
                    If .Cells(i, "B").Value <> "Argentina" Then
                        
                        .Rows(i).Resize(rowEnd - i + 1).Delete
                    End If
                        
                    rowEnd = i - 1
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Thanks a lot Bob. It worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •