Macro to copy from sheet 1 to sheet2

shri1987

New member
Joined
Aug 23, 2014
Messages
2
Reaction score
0
Points
0
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 IdArgentinaCostDesc
214563$45,000NF Bill
2345645$65,000
213455$54,000NF Bill
ProductIdTurkeyCostDesc
4567845$23,000
765436$56,000NF Bill
6754378$35,000
5437688$43,000
ProductIDArgentinaCostDesc
54634566$32,000
8765466$32,000NF Bill
4537666$23,000
ProductIDFranceCostDesc
5976545$32,000
4537655$32,000
ProductIdArgentinaCostDesc
5642332$23,000NF Bill
654733$34,000



Solution:



Product IdArgentinaCostDesc
214563$45,000NF Bill
2345645$65,000
213455$54,000NF Bill
ProductIDArgentinaCostDesc
54634566$32,000
8765466$32,000NF Bill
4537666$23,000
ProductIdArgentinaCostDesc
5642332$23,000NF Bill
654733$34,000


Thanks in Advance.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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
 
Top