“Workbook.Sheets.Add After”给出“下标超出范围”错误（VBA）

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.

我有一张四张工作簿 - 第一,第二,第三和第四。我正在尝试在每张工作表之后添加一个额外的工作表来创建数据透视表。他们将被命名为First Pivot,Second Pivot,Third Pivot和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

我能够创建First Pivot表,但是我得到了一个超出范围的下标错误。我正在使用的代码是

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).

由于某种原因,CurrSheet.Index似乎没有第二次工作,虽然它代表一个有效的数字(我使用MsgBox检查)。

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

有人可以告诉我我做错了什么吗?

1 个解决方案

#1

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.

我可以看到你得到该错误的唯一方法是,如果代码位于某个位置(例如ThisWorkbook模块),其中Worksheets(CurrSheet.Index)本身引用带有代码而不是NewBook的工作簿。 (旁白:奇怪的是它在所有情况下都能正常工作,但直到提供的索引高于包含代码的工作簿中的工作表数量才会这样做)无论如何,最好还是指定工作簿。