How to create a dynamic pivot table and refresh automatically in excel?

[Riyaz]

Private Sub Worksheet_Activate()
Sheets("sheetname").PivotTables("PivotTablename").RefreshTable

End Sub

Dear sir
I have used this vba code On a worksheet to auto refresh the pivot table on another sheet . It was working


But I have 2 more worksheets in my inventory , i am unable to do the same again for the respective worksheet and their pivot tables.

I tried to copy the same code to the respective worksheet with changing names of the sheet and table but excel shows compatibility error and not working .

Am using excel 2007 .

Kindly help me in this regard

Thank you

 

[jorismoerings]

Hi and welcome to the board,

I assume you're after automatic refreshment of the pivot tables on other sheets as soon as you activate this particular sheet.

If that's correct remove all copies of the code you've created on other sheets and change your existing code to:

Private Sub Worksheet_Activate()

    ThisWorkbook.RefreshAll

End Sub

 

[Riyaz]

Hi and welcome to the board,

I assume you're after automatic refreshment of the pivot tables on other sheets as soon as you activate this particular sheet.

If that's correct remove all copies of the code you've created on other sheets and change your existing code to:

Private Sub Worksheet_Activate()

    ThisWorkbook.RefreshAll

End Sub

Thank u very much for ur valuable reply

but the code didn't work

am not expert in using vba codes or macro in excel
ur kind help will be appreciated
Thanks in advance

 

[jorismoerings]

Just the remark "the code didn't work" isn't enough to determine what's wrong. We need some more information;

1. what's the setup of your workbook
2. where did you place the code
3. is your workbook running more code
4. what is the ultimate goal to achieve with this code.

 

[Riyaz]

Just the remark "the code didn't work" isn't enough to determine what's wrong. We need some more information;

1. what's the setup of your workbook
2. where did you place the code
3. is your workbook running more code
4. what is the ultimate goal to achieve with this code.

Thank u again sir for ur reply

sorry for my poor English ,

Actually am creating a workbook for stock
maintainence.
I have a 4 sheets
lets say sheet 1 for receiving lists
Sheet 2 for issuing list ie dealer 1
Sheet 3 for issuing list ie dealer 2
Sheet 4 for issuing list ie dealer 3
Sheet 5 for stock on site

I also created 3 sheets with pivot tables
with the datas from dealer 1,2&3(ie sheet 2,3&4).
whenever dealers issuing details is updated I want the respective
pivot tables updated automatically .

am using excel 2007!
i placed the vba code u given in sheet 2.
only this code am using in this workbook.

again sorry for my English .

Thanking u again