CODEWITHSUNDEEP
excelvba
Graeme Wilkinson
Graeme Wilkinson

Reputation: 419

Excel VBA copying rows in For loop

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?

Upvotes: 1

Views: 140

Answers (1)

Wizhi
Wizhi

Reputation: 6549

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

Upvotes: 2

Related Questions