Excel VBA copying rows in For loop

Question

Thanks for help.

I am trying to copy some rows to a new sheet and will be adding some conditions later on, but wanted to get the basics right.

I want to copy the 4th row on the wsCurrent Sheet to the 1st row on the wsReview Sheet, and then copy rows 5-200 on to that sheet too. Eventually I will be having the for loop skip based on criteria so it is only certain rows that are copied.

The first part of copying the 4th row works fine, but the for loop throws errors and doesn't do what I expect, for example, it takes the "j" as the column reference, rather than the value and if I try and use a more complex column reference, then it won't compile.

Sub exportForReview()
Dim wbCurrent As Workbook
Set wbCurrent = ThisWorkbook
Dim wsReview As Worksheet
Set wsReview = wbCurrent.Worksheets("ExportForReview")
Dim wsCurrent As Worksheet
Set wsCurrent = wbCurrent.Worksheets("Master")
Dim j As Integer
j = 2

wsCurrent.Range("4:4").Copy
wsReview.Range("1:1").PasteSpecial Paste:=xlPasteFormats
wsReview.Range("1:1").PasteSpecial Paste:=xlPasteValues

For i = 5 To 200
    wsCurrent.Cells(i).EntireRow.Copy
    wsReview.Cells(j).EntireRow.PasteSpecial Paste:=xlPasteFormats
    wsReview.Cells(j).EntireRow.PasteSpecial Paste:=xlPasteValues
    j = j + 1

Next i    
End Sub

I also tried

    For i = 5 To 200
    wsCurrent.Range("i:i").Copy
    wsReview.Range("j:j").EntireRow.PasteSpecial Paste:=xlPasteFormats
    wsReview.Range("j:j").PasteSpecial Paste:=xlPasteValues
    j = j + 1

Next i

There seems to be an issue trying to use variables inside the code to select and copy the row, can anyone advise?

How about wsReview.Cells(j,1).EntireRow.PasteSpecial ? Do you really need to copy the whole row? And why not do it in one go? — SJR
– SJR, Commented Nov 18, 2020 at 15:42
I see your point, I need to copy the whole row of the table, rather than the whole row of the sheet, so yes, I will use the range as suggested in the below to resolve that feedback point. In addition, I will be iterating through checking if some data is present within the particular row, and if not, skipping and not copying it. As I mentioned, this was just the basic bit and I am going to add some conditions so it skips, hope that's clear and thank you so much for getting back to me and giving me the advice. — Graeme Wilkinson
– Graeme Wilkinson, Commented Nov 18, 2020 at 17:02

Wizhi · Accepted Answer · 2020-11-18 16:08:01Z

2

You're so close :)

Change this part:

For i = 5 To 200
    wsCurrent.Cells(i).EntireRow.Copy
    wsReview.Cells(j).EntireRow.PasteSpecial Paste:=xlPasteFormats
    wsReview.Cells(j).EntireRow.PasteSpecial Paste:=xlPasteValues
    j = j + 1
Next i

to this:

For i = 5 To 200
    wsCurrent.Rows(i).Copy
    wsReview.Rows(j).PasteSpecial Paste:=xlPasteFormats
    wsReview.Rows(j).EntireRow.PasteSpecial Paste:=xlPasteValues
    j = j + 1
Next i

Friendly tip, is to consider to define the range! example:

wsCurrent.Range(wsCurrent.Cells(i, "A"), wsCurrent.Cells(i, "DA")).Copy 
wsReview.Range(wsReview.Cells(i, "A"), wsReview.Cells(i, "DA")).PasteSpecial Paste:=xlPasteFormats

It's large amount of data that you want to copy and paste which in large workbook will slow down the performance considerably. You'll propbably have "not responding" for a short time when you run the code.

Cells(row, column) wants a row and column value while rows() or columns() only needs a single value :)

edited Nov 18, 2020 at 16:08

answered Nov 18, 2020 at 15:54

Wizhi

6,5894 gold badges29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SJR Over a year ago

You can use Cells with a single argument, but e.g. cells(3).address will return C1 rather than A3.

Graeme Wilkinson Over a year ago

Thanks worked a treat and thanks for defining range, yes it did take a while to process the code. So glad I was close, always feel like I'll be told off for posting an obvious question on here!

Collectives™ on Stack Overflow

Excel VBA copying rows in For loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related