"Workbook.Sheets.Add After" gives "Subscript out of range" error (VBA)

Question

I have a workbook with four sheets- First, Second, Third and Fourth. I'm trying to add an extra sheet after each sheet for creating pivot tables. They would be named First Pivot, Second Pivot, Third Pivot and Fourth Pivot.

I'm able to create the First Pivot sheet but I get a Subscript out of range error. The code I'm using is

Function Pivotizer(FilePathAndName As String)
  On Error GoTo ErrorTeller
  Dim NewBook As Workbook, CurrSheet, sht As Worksheet, i, FinalCol, ColIndex As Integer, FinalRow As Long
  Dim pvtCache As PivotCache, pvt As PivotTable, StartPvt, SrcData, KountOf As String

  Set NewBook = Workbooks.Open(FilePathAndName )


  For Each CurrSheet In NewBook.Worksheets
    FinalRow = CurrSheet.Cells.Find(What:="*", After:=CurrSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
                xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    FinalCol = CurrSheet.Cells(1, CurrSheet.Columns.Count).End(xlToLeft).Column

If CurrSheet.Name = "First" Then

  NewBook.Sheets.Add After:=Worksheets(CurrSheet.Index)
  NewBook.Sheets(CurrSheet.Index + 1).Name = "First Pivot"

ElseIf CurrSheet.Name = "Second" Then
  NewBook.Sheets.Add After:=Worksheets(CurrSheet.Index)'<--- This is where I get the error
  NewBook.Sheets(CurrSheet.Index + 1).Name = "Second Pivot"
End If
  Next
  Exit Function
ErrorTeller:
  MsgBox Err.Description
End Function

For some reason, the CurrSheet.Index doesn't seem to be working the second time though it represents a valid number (I checked using a MsgBox).

Could someone please tell me what I'm doing wrong?

This code works for me till the time all existing sheets have some data in it. The reason why I say some data because, .Find will fail if there is no data in the worksheet. I have explained that Here See the use of Application.WorksheetFunction.CountA(.Cells) <> 0 in that link — Siddharth Rout

Rory Rory · Accepted Answer · 2015-05-13T11:14:11

Change this:

NewBook.Sheets.Add After:=Worksheets(CurrSheet.Index)

to this:

NewBook.Sheets.Add After:=NewBook.Worksheets(CurrSheet.Index)

The only way I can see you getting that error is if the code is in a location (e.g. ThisWorkbook module) where Worksheets(CurrSheet.Index) on its own refers to the workbook with the code and not NewBook. (Aside: it's weird that it works at all really in such cases, but it does until the supplied Index is higher than the number of sheets in the workbook containing the code) In any event, it's better to specify the workbook anyway.

"Workbook.Sheets.Add After" gives "Subscript out of range" error (VBA)

1 Answers