Pivot Table Automation - think I have issues with my Caches?

[TheRedCardinal]

My spreadsheet works as follows:

• There's a list of worksheets loaded into a collection
• The macro cycles through each worksheet in that collection
• It selects all the data in the sheet (UsedRange) then puts a pivot table in a certain column at the bottom of that table
• The pivot table is the same in each sheet

The code works well for the first sheet in the cycle, then for each other sheet it creates the pivot table in the right place, names it correctly (the name is based on the Worksheet name) but it doesn't populate either the rows or the data in the field.

I think I remember when I first started doing pivot tables that you need to manage the data cache but I never really needed to make more than one at a time before so it's never been an issue.

The headers in each sheet are the same.

Here is my code:

For Counter = 1 To OperateMonths.Count
    
    Set WS2 = WBk.Sheets(Range("RegLetter") & " - " & OperateMonths(Counter))
    
    With WS2
    
        LRow = .Cells(Rows.Count, 2).End(xlUp).Row
        Set PivotData = WS2.UsedRange
        WBk.Names.Add Name:="DataforPivot", RefersTo:=PivotData
           
    End With
    
'Create Pivot Table

Dim PivTotal As Range, PivData As Range, Source As PivotCache, Table As PivotTable

Dim PCache As PivotCache, PTable As PivotTable, PRange As Range, PFields As PivotField
Dim InsertRows As String
Dim PasteRange As Range
Dim GTotal As Long
  
    On Error Resume Next
    Application.DisplayAlerts = False
      
'Define Data Range

    Set PRange = Range("DataforPivot")
    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=WS2.Cells(LRow + 2, 7), TableName:=OperateMonths(Counter))
    Set PTable = PCache.CreatePivotTable(TableDestination:=WS2.Cells(LRow + 2, 7), TableName:=OperateMonths(Counter))

    
'Insert Row Fields -
    
    With ActiveSheet.PivotTables(OperateMonths(Counter)).PivotFields("Tax Code")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables(OperateMonths(Counter)).PivotFields("Tax Code Description")
        .Orientation = xlRowField
        .Position = 2
    End With

'Insert Data Fields

    With ActiveSheet.PivotTables(OperateMonths(Counter)).PivotFields("Net")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        .Name = "Total Value"
    End With
    
    With ActiveSheet.PivotTables(OperateMonths(Counter)).PivotFields("VAT")
        .Orientation = xlDataField
        .Position = 2
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        .Name = "Total Value"
    End With

  'Format Pivot Table

With ActiveSheet.PivotTables(OperateMonths(Counter))
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .ShowTableStyleRowStripes = True
    .TableStyle2 = "PivotStyleLight2"
    
    For Each PFields In .PivotFields
        PFields.Subtotals(1) = False
        PFields.RepeatLabels = True
    Next PFields
    
End With

Application.DisplayAlerts = True

Next Counter

OperateMonths is my collection successfully created in the previous steps.

 

[TheRedCardinal]

Update

I made the cardinal error of not turning off my error routine - switched it off and confirmed my suspicion that this is an issue with the Cache.

Cycled through the first sheet - on the second the routine crashes at this line and I get a type mismatch error:

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=WS2.Cells(LRow + 2, 7), TableName:=OperateMonths(Counter))

 

[TheRedCardinal]

Further update!

After turning off the error handling, the whole program crashed at the same line as above, when setting the PCache.

I turned the "Resume Next" line back on, and the pivot table created correctly in the worksheet on the first loop.

This now makes me wonder what I did wrong with the PCache line, and if it's even necessary?

 

[RoryA]

You're better off using an address string for the source data, rather than a range object, and you should be using your PTable variable:

For Counter = 1 To OperateMonths.Count
   
    Set WS2 = WBk.Sheets(Range("RegLetter") & " - " & OperateMonths(Counter))
   
    With WS2
   
        LRow = .Cells(Rows.Count, 2).End(xlUp).Row
        Dim DataSource as String
        DataSource = "'" & .Name & "'!" & .UsedRange.Address(referencestyle:=xlr1c1)
          
    End With
   
'Create Pivot Table

Dim PivTotal As Range, PivData As Range, Source As PivotCache, Table As PivotTable

Dim PCache As PivotCache, PTable As PivotTable, PFields As PivotField
Dim InsertRows As String
Dim PasteRange As Range
Dim GTotal As Long
 
    Application.DisplayAlerts = False
     
'Define Data Range

    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataSource)
    Set PTable = PCache.CreatePivotTable(TableDestination:=WS2.Cells(LRow + 2, 7), TableName:=OperateMonths(Counter))

With PTable   
'Insert Row Fields -
   
    With .PivotFields("Tax Code")
        .Orientation = xlRowField
        .Position = 1
    End With
   
    With .PivotFields("Tax Code Description")
        .Orientation = xlRowField
        .Position = 2
    End With

'Insert Data Fields

    With .PivotFields("Net")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        .Name = "Total Value"
    End With
   
    With .PivotFields("VAT")
        .Orientation = xlDataField
        .Position = 2
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        .Name = "Total Value"
    End With

  'Format Pivot Table


    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .ShowTableStyleRowStripes = True
    .TableStyle2 = "PivotStyleLight2"
   
    For Each PFields In .PivotFields
        PFields.Subtotals(1) = False
        PFields.RepeatLabels = True
    Next PFields
   
End With

Application.DisplayAlerts = True

Next Counter

 

[TheRedCardinal]

Thanks Rory,

This has given me two errors around the final formatting:

    For Each PFields In .PivotFields
        PFields.Subtotals(1) = False
        PFields.RepeatLabels = True
    Next PFields

I get:

"Unable to set the subtotals property of the PivotField Class" and then
"Application-defined or object-defined error" for the RepeatLabels line.

These worked before.

Thanks for your help!

 

[RoryA]

I don't think they did work before - you just had On Error Resume Next suppressing the errors.

Change .PivotFields into .RowFields

 

[TheRedCardinal]

"I don't think they did work before - you just had On Error Resume Next suppressing the errors."

What's the difference? ?

 

[TheRedCardinal]

PS Worked perfectly thanks!

 

[RoryA]

Glad to help.