Copy data from one sheet to another using VBA

in excel 2007 trying to loop through (un-fixed length) column (say C) and where row value matches (say "High"), then transfer value of cells Dx and Bx to sheet "transfer" in new row, where x is the row# where the matches are found. Assume "transfer" exists.

So far I've got this:

Public Sub CopyRows()
  Sheets("Sheet1").Select 

  'Find the last row of data
  LastRow = Cells(Rows.Count, 1).End(xlUp).Row

  'Loop through each row
  For x = 1 To FinalRow
    'Decide if to copy based on column C
    ThisValue = Cells(x, 3).Value

    If ThisValue = "High" Then
      Cells(x, 1).Resize(1, 33).Copy
      Sheets(开发者_运维百科"Transfer").Select
      NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
      Cells(NextRow, 1).Select
      ActiveSheet.Paste
      Sheets("Sheet1").Select
    End If
  Next x
End Sub 

I would not go that way. I believe setting up a filter first and copy only visible rows would work faster and be easier to code.

In fact, that's best done with advanced filter.

Create two ranges -- one for the condition (header and a cell):

*C column header* |
-------------------
High              |

And one for wanted data (header only):

*B column header* | *D column header*
-------------------------------------

Then use

range("A:D").advancedfilter xlFilterCopy, range("criteria range"), range("copy range")

Obviously, this is easier done with Excel interface (Data - Advanced Filter).