Hello,

I am writing a macro that will create two pivot tables from the same data range. I am still learning the concepts of variables and loops, and this macro is trying to do both!

I am working from an example in john walkenbach's power programming with vba (2007). His example is far more complex than my situation...so i may be using too many variables or unnecessary loops.

I want the pivot tables to be created on the same sheet("Summary") on columns 1 and 6. Right now the error is occuring after the "create pivot table" comment in the code.

Many thanks for your help!!

Option Explicit

Sub MakePivot()
'   This procedure currently makes 2 pivot tables
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim SummarySheet As Worksheet
    Dim Col As Long, i As Long
    
    Application.ScreenUpdating = False
    
'   Delete Summary sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Summary").Delete
    On Error GoTo 0
    
'   Add Summary Sheet
    Set SummarySheet = Worksheets.Add
    ActiveSheet.Name = "Summary"
    
'   Create Pivot Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Sheets(3).Range("A1").CurrentRegion)
    
    For i = 1 To 2
      For Col = 1 To 6 Step 5 '2columns
'       Create pivot table
        Set PT = ActiveSheet.PivotTables.Add( _
          PivotCache:=PTCache, _
          TableDestination:=SummarySheet.Cells(1, Col))
          
'       Add the fields
        If Col = 1 Then 'deviations count
            With PT
              .PivotFields(2).Orientation = xlColumnField
              .PivotFields(9).Orientation = xlRowField
              .PivotFields(15).Orientation = xlDataField
              .TableStyle2 = "PivotStyleMedium12"
              .DisplayFieldCaptions = False
              .RowGrand = False
            End With
        End If
        If Col = 6 Then 'deviations by employee
            With PT
              .PivotFields(2).Orientation = xlColumnField
              .PivotFields(5).Orientation = xlRowField
              .PivotFields(15).Orientation = xlDataField
              .TableStyle2 = "PivotStyleMedium12"
              .DisplayFieldCaptions = False
            End With
        End If
      Next Col
    Next i
      
End Sub