Looping through ranges

[Tim]

Hi ALL,

I’ve got this simplified macro which copies data from Range C7:G2000
and pastes it in n other ranges in the same worksheet:

Dim n As Integer, i As Integer
n = Range("L4")
For i = 1 To n
Range("B4") = Cells(7 + i, 11)
‘do something
Cells(6, 5 * i + 7) = Cells(7 + i, 11)
Range("C7:G2000").Select
Selection.Copy
Cells(7, 5 * i + 7).Select
ActiveSheet.Paste
Cells(6, 5 * i + 7).Select
‘do something
Next i

Now I’m trying to create a macro which does the opposite: Copy Data from
range L72000 Paste the copied data in C8’do somethingCopy Data from
C7:G2001 and Paste it back in L7. Then Copy Data from the next 5 columns
range Q7:U2000 Paste the copied data in C8’do somethingCopy Data from
C7:G2000 and Paste back in Q7 and so on n times. As you can see from the
above code n = Range("L4").

Any help is highly appreciated as always.

Tim

 

[Per Jessen]

Hi Tim

I'm a bit confused, first you want to copy data from C7:G2001, next time
from C7:G2000. In my solution I assume that you want to copy from C7:G2000
every time.

This should do it:

Sub test1()
Dim n As Long
Dim i As Long
Dim cOff As Long
Dim CopyRange As Range
Dim TargetCell As Range

n = Range("L4").Value
cOff = 0

For i = 1 To n
Range("L72000").Offset(0, cOff).Copy _
Destination:=Range("C8")
'Do things
Range("C7:G2000").Copy Range("L7").Offset(0, cOff)
cOff = cOff + 5
Next
End Sub

Regards,
Per

 

[Tim]

Works great.
Thank you Per!!!