Hello friends,

Maybe I am overthinking the wheel, but basically I am trying to automate the creation of multiple pivottables on one excel sheet (based on a good-sized table on another sheet).

The code is below and works...to create 34 pivottables. It fails on about the 74th iteration of the for...next statement (the 35th creation of a pivotTable).

Debugging stopped on the line below in green with the following message:

Run-time error '1004':

Unable to get the PivotTables property of the Worksheet class

Am I asking too much of Excel in this case or am I just not thinking of something in the code below?

Thoughts?



'CREATE PIVOT SHEET
Dim Pivot_Sheet As Worksheet

Set Pivot_Sheet = Sheets.Add(After:=Sheets(Sheets.Count))
Pivot_Sheet.Name = "Pivot_Sheet"

'DIM VARIABLES--------------------------
Dim numSts As Integer
Dim stRow As Integer
Dim stCol As Integer
Dim Count As Integer
Dim pivotstRow As Integer
Dim pivotstCol As Integer

Dim pivotname As String
Dim pivotdest As String
Dim ColName As String

'SET VARIABLES--------------------------
numSts = 0
stRow = 4
stCol = 2
Count = 0
pivotstRow = -3
pivotstCol = 1

Sheets("Report 1").Select
Cells(stRow, stCol).Select

For Count = 1 To 135
    If Right(Selection.Text, 3) = "Sts" Then
        ColName = Selection.Text
        numSts = numSts + 1
        pivotname = "PivotTable" & numSts
        pivotstRow = pivotstRow + 6
        pivotdest = "Pivot_Sheet!R" & pivotstRow & "C1"
                
        'Start PivotTable
        Sheets("Pivot_Sheet").Select
        Cells(pivotstRow, pivotstCol).Select
        
        ActiveWorkbook.Worksheets("Sheet1").PivotTables(pivotname).PivotCache. _
            CreatePivotTable TableDestination:=pivotdest, TableName:= _
            pivotname, DefaultVersion:=xlPivotTableVersion10
        
        With ActiveSheet.PivotTables(pivotname).PivotFields("Org Name")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables(pivotname).PivotFields( _
            ColName)
            .Orientation = xlColumnField
            .Position = 1
        End With
        'ActiveSheet.PivotTables(pivotname).AddDataField ActiveSheet.PivotTables(pivotname).PivotFields(ColName), "Count of " & ColName, xlCount
            
        Sheets("Report 1").Select
    End If
    
    Cells(stRow, stCol + Count).Select
Next