Bellatrix461
New Member
- Joined
- Sep 7, 2020
- Messages
- 8
- Platform
- Windows
Hello,
I'm creating a macro to copy an existing sheet from one workbook to a closed workbook. However, I keep getting an error message saying: "run-time error '9' subscript out of range". I assume that the issue is that in the closed workbook the sheets are named after the months in spanish (ex: "Enero", "Febrero"); however, I even tried to change the names to Sheet1, Sheet2 and I still get the same error message. What am I doing wrong?
VBA code:
Sub CopySheetToClosedWB()
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open(Environ("USERPROFILE") & "\COMPANY NAME\FOLDER NAME\FOLDER NAME\FOLDER NAME\FILE NAME.xlsx")
Sheets("Final").Copy After:=closedBook.Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("W1").Value
On Error GoTo 0
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
The sheet I'm copying is the one named "Final" that should be pasted in the closed workbook with the value in cell W1: Current month in Spanish. The debug is highlighting in yellow this line: Sheets("Final").Copy After:=closedBook.Sheets(Sheets.Count)
I will appreciate any help on this matter.
Thank you.
I'm creating a macro to copy an existing sheet from one workbook to a closed workbook. However, I keep getting an error message saying: "run-time error '9' subscript out of range". I assume that the issue is that in the closed workbook the sheets are named after the months in spanish (ex: "Enero", "Febrero"); however, I even tried to change the names to Sheet1, Sheet2 and I still get the same error message. What am I doing wrong?
VBA code:
Sub CopySheetToClosedWB()
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open(Environ("USERPROFILE") & "\COMPANY NAME\FOLDER NAME\FOLDER NAME\FOLDER NAME\FILE NAME.xlsx")
Sheets("Final").Copy After:=closedBook.Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Range("W1").Value
On Error GoTo 0
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
The sheet I'm copying is the one named "Final" that should be pasted in the closed workbook with the value in cell W1: Current month in Spanish. The debug is highlighting in yellow this line: Sheets("Final").Copy After:=closedBook.Sheets(Sheets.Count)
I will appreciate any help on this matter.
Thank you.