VBA: Create Multiple Pivot Tables using Multiple Databases

[AChuckle]

Hi

In my program there is a loop to create 6 pivot tables based off of 6 individual tabs in a final summary tab. After it creates the first table the program errors giving me Runtime error 5: Invalid Procedure Call or Argument. The line it actually stops on is in bold red below. My first guess was that there is something wrong with the data on the tab that is selected but that does not appear to be the case.


Any streamlining of the code would be appreciated as well. This is my first attempt to create a pivot table in VBA.

Dim TabName(1 To 6), PivotFieldName As String
Dim PrintCell As String
Dim oPC As PivotCache
Dim oPT As PivotTable
Dim oWS, oWS2 As Worksheet

For I = 1 To 6
Sheets(TabName(I)).Select
Set oWS2 = ActiveSheet

UsedCol = Sheets("Summary").UsedRange.Columns.Count
UsedRow = Sheets("Summary").UsedRange.Rows.Count
UsedRowData = oWS2.UsedRange.Rows.Count

If I = 1 Or I = 2 Then
PivotFieldName = "P&L SIC Assumption"
Else
PivotFieldName = "Assumed Segment"
End If

If I = 1 Then
PrintCell = "A2"
Else
PrintCell = "A" & UsedRow + 4
End If

If UsedRowData > 2 Then
Set oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, oWS2.UsedRange)
Set oPT = oPC.CreatePivotTable(oWS.Range(PrintCell), , True)
oPT.AddFields (oPT.PivotFields(PivotFieldName).Name)
oPT = oPT.AddDataField(oPT.PivotFields("HQ Duns"), , xlCount)
oPT = oPT.AddDataField(oPT.PivotFields("Estimated Local Area Employees"), , xlSum)
Else
End If
Next