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

 

[zerofeng]

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

I still have few sheets with total stats in that workbook. Hence, it won't be in actual sheet sequence but will definitely keep this vba for future references. Thanks

 

[Alex Blakenburg]

Hi Alex, it works perfectly.
Thanks.

It was only a very tweak to @myall_blues's solution. Glad it's working for you now. Thanks for letting us know.

 

[zerofeng]

It was only a very tweak to @myall_blues's solution. Glad it's working for you now. Thanks for letting us know.

Thanks @myall_blues and @Alex Blakenburg