Subscript out of range error in VBA

[UserPrabha]

I am trying to run this code, it successfully copies the first two sheets and then throws up Error 9. This is my code.

Sub Copy_Multiple_Sheets()

Dim Source As String
Dim Destination As String

Source = "TR B.xlsx"
Destination = "Load B.xlsx"

Dim Worksheets As Variant
ReDim Worksheets(12)

Worksheets(1) = "apr"
Worksheets(2) = "may"
Worksheets(3) = "june"
Worksheets(4) = "Jul"
Worksheets(5) = "Aug"
Worksheets(6) = "Sep"
Worksheets(7) = "oct"
Worksheets(8) = "nov"
Worksheets(9) = "dec"
Worksheets(10) = "jan"
Worksheets(11) = "feb"
Worksheets(12) = "mar"

Dim i As Variant
For i = 1 To UBound(Worksheets)
Workbooks(Source).Sheets(Worksheets(i)).Copy _
After:=Workbooks(Destination).Sheets(Workbooks(Destination).Sheets.count)
Next i

End Sub

This is where the error subscript out of range is pointing to . Can you please help

Workbooks(Source).Sheets(Worksheets(i)).Copy _
After:=Workbooks(Destination).Sheets(Workbooks(Destination).Sheets.count)

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Just a guess, but in your code you have the worksheet name "june" but all the others are 3 letters. What is the actual name of that sheet in the source workbook?

Also, it is a bad idea to use variable names that are already words used by vba. You have used 'Source',' Destination' and 'Worksheets' as variable names and all of those words are already used by vba. Errors often stem from that.