Pete_Bristol
Board Regular
- Joined
- Mar 8, 2006
- Messages
- 122
Dear All,
Please can you help with the following problem.
The fact that pivottables do not keep their custom formatting after refresh (despite setting properties of preserveformating etc) is an age-old problem which can be "solved" by running a user macro to reformat, associated with the PivotTableUpdate event. No problem with any of that and have been doing it successfully for years.
My current project has a single external sql query which is linked to about 10 pivottables, each on their own tab (which are initially hidden) and are "beautifully" format with subtotals, complex conditional formatting etc...the works!
The problem starts when the query, and hence the pivottables, are refreshed as this destroys all the formating. Although the pivottable update event fires, the reformatting macro does not work because it depends heavily on "pivotselect" and the tabs are hidden.
I have tried every possible solution I can think of to overcome this problem, from millions of application.screenupdating=false to Win32 API to lock and freeze the window whilst each tab is unhidden and reformat but nothing, and I mean NOTHING seems to work. The screen just dances all over the place as the macro does its stuff.
The best I can come up with is to attached my re-format macro to the worksheet_activate event so at least it gets reformat when the tab is selected but this is still "messy" as the user gets to see it update infront of their eyes!
All i want to acheive is
1. update external query (OK),
2. refresh pivottables on hidden tabs (OK),
3. reformat each so that they look professional *before* they are chosen (NOT OK)
In normal VBA there is generally never a reason to "select" before carrying out an operation but I cant seem to find any easy and quick alternative to using pivotselect.
I've struggling with this for days now and have searched the net high and low without sucess. This must be a common problem, am I overlooking something obvious?
I look forward to any replies I receive.
Thank you
Pete
Please can you help with the following problem.
The fact that pivottables do not keep their custom formatting after refresh (despite setting properties of preserveformating etc) is an age-old problem which can be "solved" by running a user macro to reformat, associated with the PivotTableUpdate event. No problem with any of that and have been doing it successfully for years.
My current project has a single external sql query which is linked to about 10 pivottables, each on their own tab (which are initially hidden) and are "beautifully" format with subtotals, complex conditional formatting etc...the works!
The problem starts when the query, and hence the pivottables, are refreshed as this destroys all the formating. Although the pivottable update event fires, the reformatting macro does not work because it depends heavily on "pivotselect" and the tabs are hidden.
I have tried every possible solution I can think of to overcome this problem, from millions of application.screenupdating=false to Win32 API to lock and freeze the window whilst each tab is unhidden and reformat but nothing, and I mean NOTHING seems to work. The screen just dances all over the place as the macro does its stuff.
The best I can come up with is to attached my re-format macro to the worksheet_activate event so at least it gets reformat when the tab is selected but this is still "messy" as the user gets to see it update infront of their eyes!
All i want to acheive is
1. update external query (OK),
2. refresh pivottables on hidden tabs (OK),
3. reformat each so that they look professional *before* they are chosen (NOT OK)
In normal VBA there is generally never a reason to "select" before carrying out an operation but I cant seem to find any easy and quick alternative to using pivotselect.
I've struggling with this for days now and have searched the net high and low without sucess. This must be a common problem, am I overlooking something obvious?
I look forward to any replies I receive.
Thank you
Pete
