Copy and Paste a range from one worksheet to multiple worksheets

Question

I am trying to copy a range from one worksheet to about 600 more worksheets in the same workbook.

I have found some code that enables me to copy the range and paste it at the end of the next worksheet. The code below only pastes the range to 1 worksheet (Sheet3). But I am unable to figure out how to loop it to paste in all the other worksheets. How can I add a loop to the code below to to do so?

Sub copypaste()
Dim i As Long
With Sheets("Sheet3")
i = .Range("B" & Rows.Count).End(3).Row
Sheets("Cert").Range("A1:K27").Copy .Range("A" & i + 1)
End With
End Sub

Do you already have the 600 worksheets? Or should the loop create them? — stenci
The worksheets are already in the workbook, but Aaron answered the question below. — Ramath0rn
possible duplicate of Using VBA Running A Loop Thru Multiple Worksheets — RubberDuck

Aaron Aaron · Accepted Answer · 2014-05-05T16:35:27

If by 600 worksheets, you mean that you would like to copy to all the worksheets, then consider:

For Each WS In Worksheets
  With WS
  i = .Range("B" & Rows.Count).End(3).Row
  Sheets("Cert").Range("A1:K27").Copy .Range("A" & i + 1)
  End With
Next WS

Copy and Paste a range from one worksheet to multiple worksheets

1 Answers