Naming Pivot Table Paramater Report

S

Shrikant

I have asked similar question some 10 days back but could not locate it now.
Pl Excuse. I also forgot to click the 'notify me' box below.

I want to give name to the Pivot Table Report just like we give names to the
'view' facility. At present I have to save the different format of the report
in separate worksheets thereby increasing the file size. Instead, I would
like to have one data sheet and only one report with a choice of different
names so that I can have same report views from different angles
e.g. Regionwise Productwise Sales, Salesmanwise monthwise Sales, Productwise
Regionwise Sales etc.
I hope I am clear.

Shrikant
 
C

Conrad Carlberg

It sounds like your intent is to get a pivot table that will display the row
and column fields differently, depending on the view you want to display.
Pivot tables don't have the type of capability that, say, scenarios have,
such that you can select a particular scenario by name. There isn't a
built-in way to select a particular pivot table arrangement by name. Your
best bet is probably to use VBA to rearrange the pivot table, with a
different procedure for each angle. Then put a button on the worksheet for
each procedure and associate each button with the appropriate procedure.

C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005
 
S

Shrikant

Thanks Conrad. It answered my question, though I was disappointed. I am not
familiar with VBS. Can I use Macros - one each for one type of report and put
it on an icon on the toolbar ?
 
C

Conrad Carlberg

Shrikant,

It's a little confusing, but "macros" and "VBA" are these days synonymous in
Office applications like Excel. Since you're not yet familiar with VBA, I'd
suggest that you do the following:

1. Have the worksheet with your pivot table active.
2. Choose Tools | Macro | Record New Macro. You'll get a dialog box where
you can set some options, and after you OK out of it a new toolbar will
appear.
3. Pivot your table so that it shows one of your preferred arrangements.
4. Click Stop Recording on the new toolbar, which will now disappear.
5. Put a button on a toolbar and point it at the code you just recorded.
Give the button a caption that indicates which arrangement it will create.
6. Repeat 2 through 5 for each arrangement you want your pivot table to take
on.

BTW, recording macros and then examining what VBA recorded in response to
your steps is a great way to become familiar with VBA and the Excel object
model. Bear in mind, though, that the macro recorder will record a lot of
statements that aren't necessarily pertinent to what you're doing. It does
that so as to be comprehensive.

C^2
Conrad Carlberg
 
Top