CODEWITHSUNDEEP
vbaloops
lekoppa
lekoppa

Reputation: 9

VBA Loop: Copy Range from Multiple Worksheets to Multiple Set Locations

I initially used the code (below) to compile data from 15 worksheets into a "template" sheet. It does this very well-- however, the needs of the workbook have changed somewhat.

Rather than copying this data into a relative location (the first empty cell in "template" column A), I now need to arrange the data into set locations, offset by 25 on each loop.

Ex:

wks 1 copy to A3 / 
wks 2 copy to A28 / 
wks 3 copy to A53 / etc

I have been trying to troubleshoot, but I'm still very weak when it comes to loops. Can anyone help me out?

Sub test()
  Dim wks As Worksheet

  For Each wks In ThisWorkbook.Worksheets

    If Not wks.Name = "template" Then
      wks.Range("B6:B30").Copy
      ActiveSheet.Paste
      Destination:=Worksheets("template").Cells(Rows.Count, "A").End(xlUp).Offset(1)

    End If

  Next
End Sub

Upvotes: 0

Views: 29

Answers (1)

SJR
SJR

Reputation: 23081

Try this. Assume the copied data is never more than 24 rows?

Sub test()

Dim wks As Worksheet, r As Range

Set r = Worksheets("template").Range("A3") 'initial paste range

For Each wks In ThisWorkbook.Worksheets
    If Not wks.Name = "template" Then
        wks.Range("B6:B30").Copy r
        Set r = r.Offset(25)                'move down 25 
    End If
Next

End Sub

Upvotes: 1

Related Questions