| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to refresh a Scenario PivotTable Report?
Worked out perfectly. Thank you in deed!
"Debra Dalgleish" wrote: Refer to the pivot table by number, and you should avoid the problem: With ActiveSheet.PivotTables(1).PivotFields("Price") Gerry wrote: I have figured out what was wrong - whenever you create a new pivot report, automatically generated name is assigned to it (pivotreport1, pivotreport2, ect.). My Macro referred to the particular pivot report name that was generated at the moment of recording. As I used the same macro to generate another pivot report, the newly assigned name did not math the name in my macro command. The sequence of automatically generated pivot report names is restarted when you reopen the file (again starts from pivotreport1). The macro must refer to "pivotreport1" - then it will work once after you reopen the file. The sample code: ActiveSheet.Scenarios.CreateSummary ReportType:=xlPivotTable, ResultCells:= _ Range("F19") Range("A8").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Price" _ ) .Orientation = xlPageField .Position = 1 End With Even this is not ideal solution, its far more than nothing. Could you suggest the code that would allow rebuilding a scenario pivot report without reopening a file each time? "Debra Dalgleish" wrote: Did you get error messages? What does the code look like, and what part of the code isn't working as you expect? Gerry wrote: Thanks again. I have also tried creating macro command that would build a new scenario pivot report. I have recorded macro command by manually creating and formating the Scenario Pivot Table Report. In result, the macro command managed to hide fields i did not need, but it failed to assign new data to pivot fields (for example, to move certain data from the column field to the row field). How could I fix this? If that is possible, it could be the solution for the problem. "Debra Dalgleish" wrote: You could also look at the Scenario Alternative sample file he http://www.contextures.com/excelfiles.html Under Scenarios, look for 'SC0002 - Alternative to Scenario' -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refresh and PivotTable with Analysis Services | Excel Worksheet Functions | |||
| Excel 2k Pivot Table refresh scenario | Excel Discussion (Misc queries) | |||
| Refresh scenario information in a pivot table | Excel Discussion (Misc queries) | |||
| Refresh Pivottable last 4 weeks data from today | Excel Worksheet Functions | |||
| why report manager ignores the range set in the scenario | Excel Discussion (Misc queries) | |||
Hybrid Mode
