PivotTable Wizard - layout

[llywelyn]

Iam using Excel 2000. When using Pivots (pivs) you can click the "layout" button when using the wizard, this is the older layout of handeling pivs. I want to add a button to the standard piv comand bar to get stright to the layout. I asume that I will need to use VBA, I'm just not sure where to start as I can't record anthing close to this action.
Thanks

 

[xenou]

1) With the PivotTable toolbar visible, select the last arrow on the toolbar, which is add or remove buttons

2) If needed, choose pivot table from the list of available menus

3) From the buttons available choose the Pivot Table and PivotChart Report button

4) If you select View | Toolbars | Customize ... you can also move this button anywhere you want it on the toolbar.

----------------------------
Alternative series of steps is:
1) View | Toolbars | Customize...
2) Choose the Pivot Table and PivotChart Report button from the Data category on the Command Tab
3) Drag it to any toolbar, including the pivot table toolbar.
----------------------------

This button works well when you have a cell selected in the pivot table:
1) Click the button
2) Click Layout

Hope this helps. AB

 

[llywelyn]

Thanks that is helpful. I would quite like to take it one step further though if posible. I would like to make a botton that takes you directly to the layout part. For this I think it will need to be done in VBA.

Thanks

 

[Andrew Poulsom]

Assuming the active cell is within a pivot table:

Sub Test()
    SendKeys "%L"
    Application.Dialogs(xlDialogPivotTableWizard).Show
End Sub

 

[llywelyn]

Thanks, that is spot on.