CODEWITHSUNDEEP
vbaexcelcopy
Jordan Harris
Jordan Harris

Reputation: 131

Excel VBA - Unable to duplicate worksheet in another workbook

From a workbook, I am attempting to open another workbook, duplicate the master worksheet in that workbook and rename it. The problem being that no matter what I try it doesn't seem to work when I Copy the master sheet.

Attempt One - Using the copy method.

Sub individualStats()

  'Initialize
  Dim app As New Excel.Application
  app.Visible = False
  Dim objWorkbook As Excel.Workbook
  Set objWorkbook = app.Workbooks.Add("S:\MH\Stats\Jordan Individual Stats.xlsm")

  'Test if Worksheet exists already
  Set wsTest = Nothing
  On Error Resume Next
  Set wsTest = objWorkbook.Worksheets("Test Worksheet")
  On Error GoTo 0 

  'If worksheet does not exist then duplicate Master and rename
  If wsTest Is Nothing Then

    objWorkbook.Worksheets("Master").Copy After:=objWorkbook.Worksheets(Worksheets.count) 
    ' ^ This is the line I get the error on.
    ActiveSheet.Name = "Test Worksheet"

  End If 

  'Save and close workbook.
  app.DisplayAlerts = False

  objWorkbook.SaveAs Filename:="S:\MH\Stats\Jordan Individual Stats.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
  objWorkbook.Close SaveChanges:=False
  app.Quit
  Set app = Nothing

  app.DisplayAlerts = True
End Sub

I have marked the line I get the error on. The error is "Run-time error '9': Subscript out of range."

Attempt Two - Calling a Macro from the Workbook

Inside of the "Jordan Individual Stats.xlsm" workbook I have created this Macro. 

Sub duplicateMaster()
   Sheets("Master").Copy After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Test Worksheet"

End Sub

This sub works completely fine if I run it within that workbook.

But when I try to call this module from the original workbook it doesn't work. 

...
If wsTest Is Nothing Then
    Application.Run ("'S:\MH\Stats\Jordan Individual Stats.xlsm'!duplicateMaster")
End If 
...

The error comes up on the line in the duplicateMaster module on the line "Sheets("Master").Copy After:=Sheets(Sheets.Count)".

The error is the same "Run-time error '9': Subscript out of range."

How can I fix this?

Upvotes: 0

Views: 1915

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

objWorkbook.Worksheets("Master").Copy _
    After:=objWorkbook.Worksheets(Worksheets.count)

here Worksheets.count will refer to the active workbook, but not in the new instance of Excel you created. It will instead refer to the active workbook in the instance where your code is running.

Try this:

objWorkbook.Worksheets("Master").Copy _
    After:=objWorkbook.Worksheets(objWorkbook.Worksheets.count)

You don't need to create a new instance of Excel to do this, and not doing so will prevent this type of easily-overlooked problem.

Upvotes: 4

Related Questions