How to adjust a Pivot Table range in Excel VBA

[Cakz Primz]

Dear all,

I have 2 sheets, one is "Data", and another is "OWL Balance"
Data is just a common range, from colomn A2 up to column AE until the lastrow, and this is not a table. Number of rows is dynamically changed. Growing up.
Currently, number of rows in "Data" sheet is 3,312 but tomorrow must be higher as some OWL (Outstanding Work List) are closed.
We used that range to create 4 (four) PivotTables in "OWL Balance" sheet, the name of the PivotTable are:
"PivotTable1"
"PivotTable2"
"PivotTable10"
"PivotTable11"

How to adjust the range in all PivotTables based on the latest number of rows in "Data" sheet? without turning the area into a Table
For the moment we do it manually changing data source range, but often we forgot to adjust, so the pivotchart gave the improper number.

Thanks so much in advance, for your kind assistance, help and attention.

Best regards,
Prima - Indonesia

 

[Domenic]

First define a dynamic named range (Ribbon >> Formulas tab >> Defined Names group >> Define Name) . . .

Name:  DataRange 'change the name as desired

Refers to:  =OFFSET(Data!$A$2:$AE$2,0,0,COUNTA(Data!$AE:$AE))

Click OK

Note, however, if cell AE1 contains a value, you'll need to adjust the formula as follows...

=OFFSET(Data!$A$2:$AE$2,0,0,COUNTA(Data!$AE:$AE)-1)

Then change the data source for each of your PivotTables so that it refers to DataRange as the range.

Hope this helps!

 

[Cakz Primz]

First define a dynamic named range (Ribbon >> Formulas tab >> Defined Names group >> Define Name) . . .

Name:  DataRange 'change the name as desired

Refers to:  =OFFSET(Data!$A$2:$AE$2,0,0,COUNTA(Data!$AE:$AE))

Click OK

Note, however, if cell AE1 contains a value, you'll need to adjust the formula as follows...

=OFFSET(Data!$A$2:$AE$2,0,0,COUNTA(Data!$AE:$AE)-1)

Then change the data source for each of your PivotTables so that it refers to DataRange as the range.

Hope this helps!

Dear Domenic,

Thank you very much for your kind assistance, help.
Your solution is working very well. Really appreciate it.
Problem solved !

Again, thank you very very much.

Best regards,
Prima - Indonesia

 

[Domenic]

You're very welcome, I'm glad I could help.

And thanks for your feedback.

Cheers!