"run-time error '9' subscript out of range" received when running macro

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
8
Platform
  1. 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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Use code tags when entering code.

When you open the "closed workbook", it becomes the active workbook and does not have a sheet named "Final".

Code:
Sub Copy_Sheet_Into_Closed_Workbook()
Dim wb1 As Workbook, wb2 As Workbook
Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
        Set wb2 = Workbooks.Open("C:\AllYourFoldersHere\WhateverWorkbookName.xlsx")
            Application.DisplayAlerts = False
                On Error Resume Next
                    If Not wb2.Sheets("Final") Is Nothing Then wb2.Sheets("Final").Delete     '<----- Deletes a sheet named final in the opened workbook if it is there. 
                On Error GoTo 0
            Application.DisplayAlerts = True
        wb1.Sheets("Final").Copy After:=wb2.Sheets(wb2.Sheets.Count)
    wb2.Close True    '<---- Save and close opened workbook
Application.ScreenUpdating = True
End Sub
 

Attachments

  • Use Code Tags MrExcel.JPG
    Use Code Tags MrExcel.JPG
    50.2 KB · Views: 4
Last edited:

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
8
Platform
  1. Windows
Thank you! It did work; however, the sheet's name appears on the wb2 also as Final. Instead, I need the value in wb1 in cell W1 to be the name of the sheet as it contains the month. So basically, the Final sheet in wb1 should be named MONTH-21 in wb2. That's why I have added this line before: ActiveSheet.Name = Range("W1").Value

How can I fix the name to the cell's W1 value?

Thanks again for your amazing help!
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
You don't mention the Sheet Name for Cell W1 but I assume it is the "Final" Sheet.
If not, change as required.

See if this does as you want.
Replace this line
Code:
wb2.Close True    '<---- Save and close opened workbook
With these lines
Code:
With wb2
        .Sheets(.Sheets.Count).Name = wb1.Sheets("Final").Range("W1").Value
       .Close True    '<---- Save and close opened workbook
    End With
 

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
8
Platform
  1. Windows

ADVERTISEMENT

Hi,
Actually, the name of the sheet where W1 cell is located is "Panel". I wrote the code like this and I'm still getting a debug:

Sub CopySheetToClosedWB()
Dim wb1 As Workbook, wb2 As Workbook

Application.ScreenUpdating = False

Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open(Environ("USERPROFILE") & "\COMPANY NAME\FOLDER NAME\FOLDER NAME\FOLDER NAME\FILE NAME.xlsx")
Application.DisplayAlerts = False
On Error Resume Next
If Not wb2.Sheets("Final") Is Nothing Then wb2.Sheets("Final").Delete
On Error GoTo 0

Application.DisplayAlerts = True
wb1.Sheets("Final").Copy After:=wb2.Sheets(wb2.Sheets.Count)
With wb2
.Sheets(.Sheets.Count).Name = wb1.Sheets("Panel").Range("W1").Value
.Close True
End With

Application.ScreenUpdating = True

End Sub

I'm getting this line highlighted in yellow: .Sheets(.Sheets.Count).Name = wb1.Sheets("Panel").Range("W1").Value

Thank you.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
No problem at this end with that code. Just tried it out.
No spelling errors? Does the "Panel" sheet have leading or trailing spaces in it's name?
"Panel" is in the Workbook that has the code in a regular module?

Use code tags when showing code in your Post.
 

Attachments

  • Use Code Tags MrExcel.JPG
    Use Code Tags MrExcel.JPG
    50.2 KB · Views: 2

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,824
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Code:
Sub CopySheetToClosedWB()
Dim wb1 As Workbook, wb2 As Workbook
Dim a As String
Set wb1 = ActiveWorkbook
a = wb1.Sheets("Panel").Range("W1").Value
Set wb2 = Workbooks.Open(Environ("USERPROFILE") & "\COMPANY NAME\FOLDER NAME\FOLDER NAME\FOLDER NAME\FILE NAME.xlsx")

    Application.DisplayAlerts = False
        On Error Resume Next
            If Not wb2.Sheets("Final") Is Nothing Then wb2.Sheets("Final").Delete
        On Error GoTo 0
    Application.DisplayAlerts = True
    
    wb1.Sheets("Final").Copy After:=wb2.Sheets(wb2.Sheets.Count)
    
    wb2.Sheets("Final").Name = a
    wb2.Close True
    
End Sub

If Cell W1 in "Panel" Sheet is formatted as Date, try this
Change the offending line to
Code:
.Sheets(.Sheets.Count).Name = Text(wb1.Sheets("Panel").Range("W1"), "MM/YYYY")
 

Bellatrix461

New Member
Joined
Sep 7, 2020
Messages
8
Platform
  1. Windows
Code:
Sub CopySheetToClosedWB()
Dim wb1 As Workbook, wb2 As Workbook
Dim a As String
Set wb1 = ActiveWorkbook
a = wb1.Sheets("Panel").Range("W1").Value
Set wb2 = Workbooks.Open(Environ("USERPROFILE") & "\COMPANY NAME\FOLDER NAME\FOLDER NAME\FOLDER NAME\FILE NAME.xlsx")

    Application.DisplayAlerts = False
        On Error Resume Next
            If Not wb2.Sheets("Final") Is Nothing Then wb2.Sheets("Final").Delete
        On Error GoTo 0
    Application.DisplayAlerts = True
   
    wb1.Sheets("Final").Copy After:=wb2.Sheets(wb2.Sheets.Count)
   
    wb2.Sheets("Final").Name = a
    wb2.Close True
   
End Sub

If Cell W1 in "Panel" Sheet is formatted as Date, try this
Change the offending line to
Code:
.Sheets(.Sheets.Count).Name = Text(wb1.Sheets("Panel").Range("W1"), "MM/YYYY")
Hello,

I did that, but got this error:

1623098818010.png


Not sure why.
 

Forum statistics

Threads
1,141,704
Messages
5,707,970
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top