Macro to copy from sheet 1 to sheet2

[shri1987]

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.

 

[Bob Phillips]

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

 

[shri1987]

Thanks a lot Bob. It worked.