Solved: Trouble looping through ranges with non-adjacent cells

[BobTurnbull]

Hello,

Typically my first post is a plea for help!

I need to loop through a large worksheet and copy cells from 7 non-adjacent columns from rows which meet a certain criteria (not shown in the code below because that part works) and paste them next to each other on a separate worksheet.

When I tried to refer to this selection of cells using Range(Cells(.... It always fell over so I have set up a separate range for each cell and a separate location to paste those cells to. My problem with the code below is that the loop works for the "rng1" to "rng7" ranges and gets new data each time but always puts that data on the original pasteRng locations (Cells(8, "A") etc) instead of moving to the next iteration of pasteRng as specified within the For-Next loop.

Here is the code:

Sub Test_What_Works()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim pasteRng1 As Range
Dim pasteRng2 As Range
Dim pasteRng3 As Range
Dim pasteRng4 As Range
Dim pasteRng5 As Range
Dim pasteRng6 As Range
Dim pasteRng7 As Range

Set rng1 = Worksheets("DATA").Cells(1, "D")
Set rng2 = Worksheets("DATA").Cells(1, "G")
Set rng3 = Worksheets("DATA").Cells(1, "J")
Set rng4 = Worksheets("DATA").Cells(1, "AG")
Set rng5 = Worksheets("DATA").Cells(1, "BD")
Set rng6 = Worksheets("DATA").Cells(1, "BN")
Set rng7 = Worksheets("DATA").Cells(1, "BO")
Set pasteRng1 = Worksheets("CancelledCases").Cells(8, "A")
Set pasteRng2 = Worksheets("CancelledCases").Cells(8, "B")
Set pasteRng3 = Worksheets("CancelledCases").Cells(8, "C")
Set pasteRng4 = Worksheets("CancelledCases").Cells(8, "D")
Set pasteRng5 = Worksheets("CancelledCases").Cells(8, "E")
Set pasteRng6 = Worksheets("CancelledCases").Cells(8, "F")
Set pasteRng7 = Worksheets("CancelledCases").Cells(8, "G")

For i = 2 To Worksheets("DATA").UsedRange.Rows.Count
rng1 = Worksheets("DATA").Cells(i, "D")
rng2 = Worksheets("DATA").Cells(i, "G")
rng3 = Worksheets("DATA").Cells(i, "J")
rng4 = Worksheets("DATA").Cells(i, "AG")
rng5 = Worksheets("DATA").Cells(i, "BD")
rng6 = Worksheets("DATA").Cells(i, "BN")
rng7 = Worksheets("DATA").Cells(i, "BO")
pasteRng1 = Worksheets("CancelledCases").Cells(5 + i, "A")
pasteRng2 = Worksheets("CancelledCases").Cells(5 + i, "B")
pasteRng3 = Worksheets("CancelledCases").Cells(5 + i, "C")
pasteRng4 = Worksheets("CancelledCases").Cells(5 + i, "D")
pasteRng5 = Worksheets("CancelledCases").Cells(5 + i, "E")
pasteRng6 = Worksheets("CancelledCases").Cells(5 + i, "F")
pasteRng7 = Worksheets("CancelledCases").Cells(5 + i, "G")
rng1.Copy pasteRng1
rng2.Copy pasteRng2
rng3.Copy pasteRng3
rng4.Copy pasteRng4
rng5.Copy pasteRng5
rng6.Copy pasteRng6
rng7.Copy pasteRng7
Next i

End Sub

Can anyone identify what I could do differently?

Thanks very much in advance.

Bob.

[Aflatoon]

You are missing Set statements
For i = 2 To Worksheets("DATA").UsedRange.Rows.Count
Set rng1 = Worksheets("DATA").Cells(i, "D")
Set rng2 = Worksheets("DATA").Cells(i, "G")
Set rng3 = Worksheets("DATA").Cells(i, "J")
Set rng4 = Worksheets("DATA").Cells(i, "AG")
Set rng5 = Worksheets("DATA").Cells(i, "BD")
Set rng6 = Worksheets("DATA").Cells(i, "BN")
Set rng7 = Worksheets("DATA").Cells(i, "BO")
Set pasteRng1 = Worksheets("CancelledCases").Cells(5 + i, "A")
Set pasteRng2 = Worksheets("CancelledCases").Cells(5 + i, "B")
Set pasteRng3 = Worksheets("CancelledCases").Cells(5 + i, "C")
Set pasteRng4 = Worksheets("CancelledCases").Cells(5 + i, "D")
Set pasteRng5 = Worksheets("CancelledCases").Cells(5 + i, "E")
Set pasteRng6 = Worksheets("CancelledCases").Cells(5 + i, "F")
Set pasteRng7 = Worksheets("CancelledCases").Cells(5 + i, "G")

[BobTurnbull]

It works!

Thanks so much for your help. Bit embarassed it was such an easy solution, I thought you only "Set" the variables at the beginning then just made them equal to whatever you wanted within loops. Glad to be corrected.

Bob.