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.

 

[zerofeng]

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

Hi blue,

The copy and paste doesn't work.
I have 1 sheet JAN24 only with data in Master and JAN24-DEC24 in Report. The data not paste to the JAN24 Report sheet.

 

[Alex Blakenburg]

Try uppercasing sMonth
If Sheets(j).Name = UCase(sMonth & 24) Then

 

[jolivanes]

@zerfeng
Why 450 in a2:h450?
High numbers like that look conspicuous.

 

[myall_blues]

The copy and paste doesn't work.
I have 1 sheet JAN24 only with data in Master and JAN24-DEC24 in Report. The data not paste to the JAN24 Report sheet.

In fairness that bit didn't change, so it never worked. Try @Alex Blakenburg's suggestion.

 

[jolivanes]

If your worksheets in both workbooks are, from left to right, named for each month, you don't need to refer to them by name.
Sheets(1) will be JAN, Sheets(2) will be FEB etc. etc.
So

Sub Populate_From_Term()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks("Master 2024.xlsm")
Set wb2 = Workbooks("Term 2024.xlsm")
    wb1.Sheets(4).Cells(2, 1).Resize(wb2.Sheets(4).UsedRange.Rows.Count - 1, 8).Value = wb2.Sheets(4).Cells(2, 1).Resize(wb2.Sheets(4).UsedRange.Rows.Count - 1, 8).Value
End Sub

will copy the used range data from columns A to H in Term APR24 sheet into Master APR24 sheet.

You can change the 4 (Sheets(4)) to represent the index number of the active sheet if you want so you don't need to change the code.
You should ensure that your sheet naming is consistent.
In your first post it shows not to be so. Capitals and small letters in JAN24 to Dec24
If you want to, you can run below macro in both workbooks. Try on a copy of each workbook first to ensure that the result is what you need.

Sub Name_Sheets()
Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        Sheets(i).Name = UCase(Application.GetCustomListContents(3)(i)) & "24"
    Next i
End Sub

 

[zerofeng]

Try uppercasing sMonth
If Sheets(j).Name = UCase(sMonth & 24) Then

Hi Alex, it works perfectly.
Thanks.