Hi,

I am currently trying to create a multiple pivot tables in one sheet.

The current formula to make a single pivot table that i am using is

Formula: copy to clipboard 


Dim PT01 As PivotTable

Dim PTCache As PivotCache

Dim FinalRow As Long

Dim FinalCol As Long

Dim PRange As range



FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = Cells(1, 1).Resize(FinalRow, FinalCol)



Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, PRange)


The current problem i have is... i need to Dim PT01 as pivottable, Dim PT02 as pivottable, Dim PT03 as pivottable and so on.... Repetition is not good!

and secondly,the total pivot table is not constant each week. During busy week, i need to create 8 pivot table and in a slow week, it can go down to 5.

See below of my current formula...
Formula: copy to clipboard 


For X1 = 2 To EOR1 Step 1

        Dim PT & x2 As PivotTable    '>>> DOES NOT WORK

    X2 = X2 + 1

Next X1


Can anyone suggest me how to create pivot table in a better way?

Thanks
Lex