Creating multiple PivotTables in a wb using multiple data sources (VBA/XL07/Vista)

[y2gordo]

Howdy folks,

I've been working on this problem for quite a while, and I can't get it to work properly. I am creating a macro for someone where data is consolidated from multiple worksheets into two summary sheets. From there, based on options selected on a "setup" worksheet, multiple PivotTables will be created from those summary sheets (and later charts created from those PvtTbls, but I'm not there yet).

Anyway, I am at the point where I can get the program to create the first PivotTable properly. However, the program is locking up with an "Application-defined or Object-defined error" when I get to the point of creating the second table.

What is posted below is the code in its current form. I created the arrays thinking that Excel might need to place the Caches and arrays in separate elements. Previously, I repeated a "ActiveSheet.PivotCaches.Create( . . . ).CreatePivotTable . . . line in the second section and received the same error.

Function CreatePivotTables(ByVal outputSheet As String, ByVal rptTypes As String, ByVal chartSplitCat As String, ByVal exCatCount As Integer, ByVal sheetType As String, ByVal fieldType As String, ByRef extraCats() As String) As Integer
' This sub creates the PivotTables from the Output Sheet
    Dim i, j, k, l As Integer
    Dim splitRows() As Integer     ' This stores the row numbers where the data splits occur
    Dim totalColumns As Integer     ' This stores the number of columns contained in the report(s)
    Dim splitName As String
    Dim tableNames() As String
    Dim outputSheetMarker As Range  ' This cycles through the output sheet(s) to find column/row names where needed
 
    ' PivotTable variables
    Dim pivotTableCaches() As PivotCache  ' Stores PT caches
    Dim pivotTableArray() As PivotTable     ' Stores PT objects
    Dim tableDataRange() As Range           ' Stores Ranges on the summary sheets where the PT data is located
 
    ' For clarity, removed this section as it only sets up variables, defines boundaries and PivotTable rows
 
    ' This section detemines the number of PvtTbls to create and sizes the arrays accordingly.  (It does so correctly.)
 
    ' PivotTables are named by the value of the category where the data split occurs (splitName), "Pivot" and the type of sheet the Table is created from.
 
    ' Now we are ready to cycle through the splitRows and create the PivotTables
    For j = 0 To (UBound(splitRows) - 1)
        ' First, we need to create the table name
        If (rptTypes = "Absolute Only" Or rptTypes = "AbsAndRel") Then
            ' Create the table name
            splitName = Sheets(outputSheet & " - Abs Date").Cells(splitRows(j) + 1, i + 1).Value
            tableNames(k) = splitName & "PivotTableAbs"
            ' Set the data range
            Set tableDataRange(k) = Sheets(outputSheet & " - Abs Date").Cells(splitRows(j), 1).Resize(splitRows(j + 1) - 1, totalColumns)
            Set pivotTableCaches(k) = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tableDataRange(k))
 
           Set pivotTableArray(k) = pivotTableCaches(k).CreatePivotTable(TableDestination:="", tableName:=tableNames(k)) 
           ' NOTE: The line above above properly creates the PivotTable object here, but fails in the next section.
 
           ' NOTE: Removed code here adds columns and data rows to the created PivotTable
 
        ' Since the PT is created on a new, generically-named sheet, removed code here determines if the final name for the sheet matches one that already exists and deletes that sheet if so, then renames the ActiveSheet to the desired name.
 
        k = k + 1
 
        End If
 
        If (rptTypes = "Relative Only" Or rptTypes = "AbsAndRel") Then
            splitName = Sheets(outputSheet & " - Rel Date").Cells(splitRows(j) + 1, i + 1).Value
            tableNames(k) = splitName & "PivotTableRel"
            Set tableDataRange(k) = Sheets(outputSheet & " - Rel Date").Cells(splitRows(j), 1).Resize(splitRows(j + 1) - 1, totalColumns)
            Set pivotTableCaches(k) = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tableDataRange(k))
 
[B]' ********* NOTE: ************[/B]
[B]' This is the line where the program breaks[/B]
[B]          Set pivotTableArray(k) = pivotTableCaches(k).CreatePivotTable(TableDestination:="", tableName:=tableNames(k))   [/B]
[B]' ***********************[/B]
 
' NOTE: The code here is a copy of the code from above that adds columns and data rows                        
 
' Repeat of the sheet naming procedures from the first section
 
            k = k + 1   ' Increments k to the next Cache/Table array position
        End If
 
        Next j
 
End Function

Like I said, I have tried this a thousand ways, and I do not understand why the same code works in the first section and fails in the second.

I should point out that on the first PvtTbl creation, the program is pulling data from the first section of one of the summary sheets. In the second creation (the one that fails), it is pulling data from a different sheet, though the range of data on that sheet is the same. The program will then loop through again to pull the next range of data from the first sheet and then the second sheet, and then repeat that process until the bottom of the summarized data is reached.

Hope you could follow all of this. I am willing to bet it is something very simple that I am overlooking (this is my first time coding PivotTables in VBA), but hopefully you guys can help me fix this.

Thanks.