How to refresh a Scenario PivotTable Report?

[Gerry]

Hi,

I am trying to solve the following issue:

My excel workbook includes two kind of input values:

1) Assumptions (I am using a Scenario PivotTable Report to track the effect
of different assumption sets on the "result cell");
2) Background data (Its values also affect the "result cell" and they are
changing time to time. I can not create scenarios for every background data
cell, as there are too many of them).

Every time background data change (quite often), I must rebuild Scenario
PivotTable Report, as it does not take into account the updated values.

Is there any quick way to refresh a Scenario PivotTable Report?

Thanks in advance,

Gerry

 

[Gerry]

I forgot to mention - I am using MS Excel 2003

 

[Debra Dalgleish]

Unfortunately, there's no way to refresh a Scenario Pivot Table.
If the information changes, you can create a new copy of the pivot table.

 

[Debra Dalgleish]

You could also look at the Scenario Alternative sample file here:

http://www.contextures.com/excelfiles.html

Under Scenarios, look for 'SC0002 - Alternative to Scenario'

 

[Gerry]

Thanks.

Unfortunately, there's no way to refresh a Scenario Pivot Table.
If the information changes, you can create a new copy of the pivot table.

 

[Gerry]

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]

Did you get error messages?

What does the code look like, and what part of the code isn't working as
you expect?

 

[Gerry]

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]

Refer to the pivot table by number, and you should avoid the problem:

With ActiveSheet.PivotTables(1).PivotFields("Price")

 

[Gerry]

Worked out perfectly. Thank you in deed!

Refer to the pivot table by number, and you should avoid the problem:

With ActiveSheet.PivotTables(1).PivotFields("Price")