Run-time error 9

[zerofeng]

Hi Experts here,

Please help.
I have 2 workbooks below and I would like to copy worksheets from workbook 1(Master) to workbook 2( Term).
I have pre-created the sheets JAN24 to Dec24 for workbook 2.(Term)

As the Master workbook sheet(by months) is actually manually created whenever come to the particular month. I got run time error 9.
Anyway to solve it?
Thanks

Sub copydata()

Dim I As Long
Dim sMonth As String
Set Master = Workbooks("Master 2024.xlsm")
Set Report = Workbooks("Term 2024.xlsm")

For I = 1 To 12
sMonth = Format(DateSerial(Year(Date), I, 1), "mmm")
Master.Worksheets(sMonth & 24).range("a2:h450").copy
Report.Worksheets(sMonth & 24).range(a2:h450). PasteSpecial paste:=xlPasteValues
Next

End sub

 

[myall_blues]

Like this...

Sub copydata()

    Dim I As Long, j As Long
    Dim sMonth As String
    Dim Master As Workbook, Report As Workbook
    Set Master = Workbooks("Master 2024.xlsm")
    Set Report = Workbooks("Term 2024.xlsm")
    
    For I = 1 To 12
        sMonth = Format(DateSerial(Year(Date), I, 1), "mmm")
        For j = 1 To Master.Sheets.Count
            If Sheets(j).Name = sMonth & 24 Then
                Master.Worksheets(sMonth & 24).Range("a2:h450").Copy
                Report.Worksheets(sMonth & 24).Range("a2:h450").PasteSpecial Paste:=xlPasteValues
                Exit For
            End If
        Next
    Next
End Sub

 

[Jason5710]

You can try:
Report.Worksheets(sMonth & 24).range("a2:h450"). PasteSpecial paste:=xlPasteValues

or:
Report.Worksheets(sMonth & 24).range("a2"). PasteSpecial paste:=xlPasteValues

 

[myall_blues]

@Jason5710 that won't fix OP's issue. Error 9 (subscript out of range) occurs when trying to refer to a non-existent object or variable. In the case shown, the error occurs because the OP code is doing all 12 months (for I = 1 to 12) but in the master spreadsheet the months only exist up to the current month.

As the Master workbook sheet(by months) is actually manually created whenever come to the particular month. I got run time error 9.