Copy data from one sheet to another using VBA

Question

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

Worded exactly as a student assignment. Please share what you've got so far. — GSerg, Jun 10 '11 at 11:13
Sorry - i am a student in the sense that i am novice in VBA and trying to pieces together a working piece of code; managed to get a the following to copy whole rows: — Mike, Jun 10 '11 at 12:05
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 — Mike, Jun 10 '11 at 12:06
You should try to avoid using the `.Select` statement for most cases. Everything should be qualified with a parent object, so instead of `Cells(x, 1)` you should do `Worksheets("Sheet1").Cells(x, 1)`. To that end the `With ... End With` is a great way to easily qualify blocks of code. — Hari Seldon, Jun 10 '11 at 13:31

score 1 · Answer 1 · answered Jun 10 '11 at 13:06

1

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.

answered Jun 10 '11 at 13:06

Juliusz

2,096
2
27
32

score 1 · Answer 2 · answered Jun 10 '11 at 13:13

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).

Copy data from one sheet to another using VBA

2 Answers2

Linked