Macro for creating two pivot tables

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

Hi,

I find that using the record Macro function is really useful. Start the recording, do what you need to do manually, stop the recording and then use the code that was stored in the marcro as a 'starter for 10' usually works for me with a bit of tweaking.

ta

PKDJ

Unfortunately, the macro recorder does not use loops or variables...as far as i know. I have accomplished the task of using the same pivot cache to produce two pivot tables, although not simultaneously. Currently, my macro creates a pivot table, copys/pastes to a new sheet, then clears the pivot table fields and creates a new pivot table without reselecting the data, and finally copys/pastes to the recently created sheet.

I want the macro to use loops and variables because soon i will want it to create three or more tables.