Problem refreshing pivot in seperate worksheet

[password]

Hi

I have some code running under the Worksheet_Change function in object Sheet1 in a workbook. This code updates cells in sheet1 when data is pasted into sheet1. This works fine.

As a result of changes to sheet1 (occuring when user pastes data) I want the Pivot tables in sheet2 to refresh automatically (these pivot table summarise the data in sheet1).

I tried adding code directly to my code in Sheet1 object but got the error message saying 'subscript out of range'. Am guessing I cannot reference Sheet2 when I'm in the Sheet1 object???

I also tried adding this 'refresh' code to a module and then calling it from the code in Sheet1 object but again get 'subscript out of range'.

Where can I put this code to get it to work?

Cheers

Nick

 

[PHV]

Where can I put this code to get it to work
Which code ....?
Brute force method:
Application.Calculate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[password]

Hi

"Which code?" is a another question I need answering but I was trying.

WorkSheet("Sheet2").Activate
ActiveWorksheet.RefreshAll

Just tried your Application.Calculate suggestion but pivots did not refresh.

Nick

 

[password]

Hi

"Which code?" is a another question I need answering but I was trying.

WorkSheet("Sheet2").Activate
ActiveWorksheet.RefreshAll

Just tried your Application.Calculate suggestion but pivots did not refresh.

Nick

 

[PHV]

Replace this:
WorkSheet("Sheet2").Activate
By this:
WorkSheet[highlight]s[/highlight]("Sheet2").Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[password]

PH

Frustrating, but correct. Thanks for the pointer.

Regards

Nick