I need a second pair eyes because the code I have written to generate pivot tables on different sheets is ... not working. Here is the code below:
Private Sub DynamicPivot()
Dim pvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim pvtTbleCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
Dim i As Integer
Dim j As Integer
For i = 1 To 3 'step for each sheet
For j = 4 To 6 'step for outbound sheets
' Determine the data area
Set wsData = Sheets("RPT_" & i)
' Set the output location
Set wsPvtTbl = Worksheets("Sheet" & j)
' Looking for existing Pivot Tables and deleting them upon a user's agreement to do so
For Each pvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable !", vbYesNo) = vbYes Then
pvtTbl.TableRange2.Clear
End If
Next pvtTbl
' Determining the Range for the Pivot Table to capture