1

I have searched for this issue and from the other threads it seem to relate to protected sheets, hidden sheets or incorrect file names but none seem to be the problem for me.

I am only having this issue with one spreadsheet file. This workbook only has one sheet and I am trying to add a sheet called "Sheet4".

I have tested with multiple other spreadsheets and it worked.

Here is the code:

Sub TestAddingSheet()


Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim WbName As String

WbName = ActiveWorkbook.Name

Set wb1 = Workbooks(WbName)
Set wb2 = Workbooks.Open("C:\test\test.xlsx")

Set sh1 = wb1.Sheets.Add(After:=Sheets(Sheets.Count))
sh1.Name = "Sheet4"
Set sh2 = wb1.Worksheets(1)

End Sub

The way I can get it to work is to activate wb1 before adding a new sheet as follows:

Sub TestAddingSheet()


Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim WbName As String

WbName = ActiveWorkbook.Name

Set wb1 = Workbooks(WbName)
Set wb2 = Workbooks.Open("C:\test\test.xlsx")

wb1.Activate
Set sh1 = wb1.Sheets.Add(After:=Sheets(Sheets.Count))
sh1.Name = "Sheet4"
Set sh2 = wb1.Worksheets(1)

End Sub

Why is the original code not working for all spreadsheets?

F4N3
  • 11
  • 2
  • 4
    `Sheets` implicitly refers to the `ActiveWorkbook`, and opening `wb2` makes it active. You just need `wb1.Sheets.Add(After:=wb1.Sheets(wb1.Sheets.Count))` – Comintern Mar 01 '19 at 16:18
  • Do you have charts or other non-worksheet objects? That's the only conceivable way that I could see a subscript error on that line. – Comintern Mar 01 '19 at 16:43
  • Thanks this worked! Do you know why it was working for some spreadsheets and not others? – F4N3 Mar 01 '19 at 16:44
  • It has nothing to do with the file - it has to do with what is active. The answers for the linked duplicate explain more. – Comintern Mar 01 '19 at 16:45
  • Consider using the `Worksheets` collection instead of `Sheets` though. – Mathieu Guindon Mar 01 '19 at 17:06

0 Answers0