vba code to refresh pivot table.

[mlaxx]

hy. i use this code in my sheet :

Private Sub Worksheet_Change(ByVal Target As Range)
Workbooks("centralizator.xlsm").Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh

End Sub

that make an automatic update for the pivottable1 from sheet 1 , centralizator.xlsm file. but when i try to make a new pivot table - pivottable2 - in another workbook the formula dont work. i get error: out of range. so...some help?

 

[vletm]

mlaxx
As You have written ... in code:
You're refreshing 'centralizator.xlsm'-file's worksheet 'Sheet1's PivotTable1 with that code - okay.

Maybe Your another file is not same name or how?
Do it also have worksheet 'Sheet1' .. it's possible.
You're trying to refresh another name of PivotTable 'pivottable2'... cannot do that, because there is PivotTable1 or how?

>> Some of those another files names do not match!
>> >> Try to modify names as You've written and test again.

 

[mlaxx]

ok.
I attach to exemplify the excel files.
The idea is as follows: The pivottable data from the pcone.xlsm file and the pctwo.xlsm pivot file is the macheta.xlsm file. If the vache code in the macheta.xlsm file only uses "

Private Sub Worksheet_Change (ByVal Target As Range)
Workbooks ("pcone.xlsm"). Worksheets ("Sheet1"). PivotTables ("PivotTable1"). PivotCache.Refresh
End Sub

"
then pivotable1 in the pcone.xlsm file updates automatically whenever you enter data in macheta.xlsm
But if I use the following voucher code "

Private Sub Worksheet_Change (ByVal Target As Range)
Workbooks ("pcone.xlsm"). Worksheets ("Sheet1"). PivotTables ("PivotTable1"). PivotCache.Refresh
Workbooks ("pctwo.xlsm"). Worksheets ("Sheet1"). PivotTables ("PivotTable2"). PivotCache.Refresh
End Sub

"
pivot table1 in the pcone.xlsm file no longer updates, nor pivotable2 from the pctwo.xlsm file.
I get the "out of the range" error. So where do I get wrong?

 

[vletm]

mlaxx
hmm..?
There is one code - in macheta.xlsm - okay?
Which TRIES to run it everytime You have changed something in Sheet1 - okay?
It runs okay ... if both pcone.xlsm and pctwo.xlsm are open!
If any of those above file is closed ... code fails.
Why do You need to try to refresh closed files?

 

[mlaxx]

wowww. both file need to be opened! kaboom. thxxxxxxxxxxxxx vletm. pure genius