Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet

[dvward]

Hi I am trying to copy a sheet from a closed workbook to my active workbook as a new sheet at the end, but I keep getting Subscript of Range error. The active workbook is a weekly report with variable dates in the name. I need to re-use this code without specifying the name of the active workbook. Is this possible? Thanks in advance!
Here is part of my code:

Dim sourceBook As Workbook
Application.ScreenUpdating = False
Set sourceBook = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
sourceBook.Sheets(List).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
sourceBook.Close Application.ScreenUpdating = True
Columns("A:B").EntireColumn.AutoFit
Sheets(1).Select
Range("H2").Select
Application.CutCopyMode = False
Columns("O:O").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("A:P").Select
Columns("A:P").EntireColumn.AutoFit

 

[6StringJazzer]

sourceBook.Sheets(List).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

I am guessing you do not have a variable called List and that is actually the name on the tab for the worksheet you want to copy. Add quotes:

sourceBook.Sheets("List").Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

 

[DanteAmor]

Hi @dvward

In which line of code do you have the error.
Also like @6StringJazzer , I guess it's this line:
sourceBook.Sheets(List).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

If the sheet is called "List" then it must be enclosed in quotes. But you must also change activeworkbook to an object, since when you open a workbook, the latter is the active one.

Try:

Sub copy_sheet()
  Dim sourceBook As Workbook
  Dim destinBook As Workbook
 
  Application.ScreenUpdating = False
 
  Set destinBook = ActiveWorkbook
  Set sourceBook = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
  sourceBook.Sheets("List").Copy After:=destinBook.Sheets(destinBook.Sheets.Count)
  sourceBook.Close
 
  Columns("A:B").EntireColumn.AutoFit
  Sheets(1).Select
  Range("H2").Select
  Application.CutCopyMode = False
  Columns("O:O").Select
  Selection.Cut
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight
  Columns("A:P").Select
  Columns("A:P").EntireColumn.AutoFit

  Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

you must also change activeworkbook to an object, since when you open a workbook, the latter is the active one.

Oh, that is a good point. I missed it. I was too focused on what was causing the error.

 

[jolivanes]

You might want to get rid of the "select" in your code.

Sub Maybe_So()
Dim wbS As Workbook, wbD As Workbook
Dim shL As Worksheet, thisSh As Worksheet
Set thisSh = ActiveSheet
Set wbD = ThisWorkbook    '<------ Workbook where you run the macro from, has the code in it and receives the sheet
Application.ScreenUpdating = False
Set wbS = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
Set shL = wbS.Worksheets("List")
shL.Copy After:=wbD.Worksheets(Worksheets.Count)
wbS.Close False
With wbD
    .Worksheets(.Worksheets.Count).Columns("A:B").AutoFit
        With wbD.Sheets("Sheet1")
            .Columns("O:O").Cut
            .Columns("B:B").Insert Shift:=xlToRight
            .Columns("A:R").AutoFit
        End With
End With
thisSh.Select
Application.ScreenUpdating = True
End Sub

 

[dvward]

Sub Maybe_So() Dim wbS As Workbook, wbD As Workbook Dim shL As Worksheet, thisSh As Worksheet Set thisSh = ActiveSheet Set wbD = ThisWorkbook '<------ Workbook where you run the macro from, has the code in it and receives the sheet Application.ScreenUpdating = False Set wbS = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx") Set shL = wbS.Worksheets("List") shL.Copy After:=wbD.Worksheets(Worksheets.Count) wbS.Close False With wbD .Worksheets(.Worksheets.Count).Columns("A:B").AutoFit With wbD.Sheets("Sheet1") .Columns("O:O").Cut .Columns("B:B").Insert Shift:=xlToRight .Columns("A:R").AutoFit End With End With thisSh.Select Application.ScreenUpdating = True End Sub

jolivanes, thank you for the code.​

However, I received an run-time error '1004', Method 'Copy' of object'_Worksheet' failed. Bold line below is the error line. Any ideas?
Dim wbS As Workbook, wbD As Workbook
Dim shL As Worksheet, thisSh As Worksheet
Set thisSh = ActiveSheet
Set wbD = ThisWorkbook '<------ Workbook where you run the macro from, has the code in it and receives the sheet
Application.ScreenUpdating = False
Set wbS = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
Set shL = wbS.Worksheets("List")
shL.Copy After:=wbD.Worksheets(Worksheets.Count)
wbS.Close False
With wbD

 

[DanteAmor]

Oh, that is a good point. I missed it. I was too focused on what was causing the error.

Thanks Jeff for looking at my answer. I hope you have a nice week.

 

[dvward]

jolivanes, thank you for the code.​

However, I received an run-time error '1004', Method 'Copy' of object'_Worksheet' failed. Bold line below is the error line. Any ideas?
Dim wbS As Workbook, wbD As Workbook
Dim shL As Worksheet, thisSh As Worksheet
Set thisSh = ActiveSheet
Set wbD = ThisWorkbook '<------ Workbook where you run the macro from, has the code in it and receives the sheet
Application.ScreenUpdating = False
Set wbS = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
Set shL = wbS.Worksheets("List")
shL.Copy After:=wbD.Worksheets(Worksheets.Count)
wbS.Close False
With wbD

You might want to get rid of the "select" in your code.

Sub Maybe_So()
Dim wbS As Workbook, wbD As Workbook
Dim shL As Worksheet, thisSh As Worksheet
Set thisSh = ActiveSheet
Set wbD = ThisWorkbook    '<------ Workbook where you run the macro from, has the code in it and receives the sheet
Application.ScreenUpdating = False
Set wbS = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
Set shL = wbS.Worksheets("List")
shL.Copy After:=wbD.Worksheets(Worksheets.Count)
wbS.Close False
With wbD
    .Worksheets(.Worksheets.Count).Columns("A:B").AutoFit
        With wbD.Sheets("Sheet1")
            .Columns("O:O").Cut
            .Columns("B:B").Insert Shift:=xlToRight
            .Columns("A:R").AutoFit
        End With
End With
thisSh.Select
Application.ScreenUpdating = True
End Sub

 

[dvward]

Not sure what you mean by 'get rid of "select" in your code'. The line with the error is 'shL.Copy After:=wbD.Worksheets(Worksheets.Count)'. I copied your code. Please help me understand.

 

[dvward]

Hi @dvward

In which line of code do you have the error.
Also like @6StringJazzer , I guess it's this line:
sourceBook.Sheets(List).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

If the sheet is called "List" then it must be enclosed in quotes. But you must also change activeworkbook to an object, since when you open a workbook, the latter is the active one.

Try:

Sub copy_sheet()
  Dim sourceBook As Workbook
  Dim destinBook As Workbook
 
  Application.ScreenUpdating = False
 
  Set destinBook = ActiveWorkbook
  Set sourceBook = Workbooks.Open("C:\Users\SESA47205\Documents\Sales Job Code List.xlsx")
  sourceBook.Sheets("List").Copy After:=destinBook.Sheets(destinBook.Sheets.Count)
  sourceBook.Close
 
  Columns("A:B").EntireColumn.AutoFit
  Sheets(1).Select
  Range("H2").Select
  Application.CutCopyMode = False
  Columns("O:O").Select
  Selection.Cut
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight
  Columns("A:P").Select
  Columns("A:P").EntireColumn.AutoFit

  Application.ScreenUpdating = True
End Sub

Thank you! destinBook worked great!