Excel VBA - Copy range from one sheet paste to all sheets after certain sheet in workbook

Question

I feel like this is too simple to be stuck on, but I have a workbook with about 100 sheets, and I need to copy a range from one sheet (Sheet2 Range a1:H200) to Sheet5 AF1:AM200 and every sheet after (Sheet5 through Sheet100 or more). I have tried creating a loop and copying the original range and pasting to each sheet, but it hasn't worked. I feel like this is the closest I've gotten

Sub CopyPasteLoop()

Dim wsVar As Worksheet

For Each wsVar In ThisWorkbook.Sheets
   With wsVar
      ThisWorkbook.Worksheets("Sheet2").Range("A1:H200").Value = ThisWorkbook.Worksheets("Sheet5").Range("AF1").Value
   End With
Next wsVar

End Sub

I feel like it should be simpler, but I can't make it work. Thanks!

Answer 1

Almost there. Try this:

Sub CopyPasteLoop()

Dim wsVar As Worksheet

Dim i as Integer
For i = 5 to ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Range("AF1:AM200").Value = ThisWorkbook.Worksheets("Sheet2").Range("A1:H200").Value
Next i

End Sub

Or for better performance, use this:

Dim vRange as Variant
vRange = ThisWorkbook.Worksheets(2).range("A1:H200")

Dim i as Integer
For i = 5 to ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Range("AF1:AM200").Value = vRange
Next i

Answer 2

Hopefully @Scott Holtzman's answer will work for you (providing your sheets are indexed in the same order as they're named). This approach will also work.

Dim wb As Workbook, ws As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set rng = wb.Sheets("Sheet2").Range("A1:H200")

For Each ws In wb.Sheets

    If CInt(Right(ws.Name, Len(ws.Name) - Len("Sheet"))) >= 5 Then

        ws.Range("AF1:AM200").Value = rng.Value

    End If

Next ws