Copy and Paste Value to another worksheet

Hi,

One way

Sub CopyPaste()
    Sheet2.Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=">2000"
    Sheet2.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Sheet1.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
End Sub

Hi,

I'm going to assume you will want the name and the amount. So:

Sub CopyPaste()
    Sheet2.Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=">2000"
    Sheet2.Range("A1").CurrentRegion.Offset(1, 0).Resize(, 2).SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Sheet1.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
End Sub

If however you did mean just the amount column then

Sub CopyPaste()
    Sheet2.Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:=">2000"
    Sheet2.Range("A1").CurrentRegion.Offset(1, 1).Resize(, 1).SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Sheet1.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
End Sub

However have you considered using a Pivot Table which will be far more flexible