pivot table within macro

[LiamF]

I'm using a macro to format some data and then put it into a pivot table. The data will have the same number of columns each time but the number of rows will vary. Can this variation be accomodated within the macro?

any help appreciated.
Liam

[T-J]

The simplest way is to use a dynamic named range and set up the Pivot Table using the Excel menus.

This uses the example in Excel help topic 'About PivotTable reports', with column headings in A1, B1 & C1.

1. Create a dynamic named range e.g. 'PivotTableData' that refers to the data to analyse.
Insert > Name > Define - PivotTableData
Refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3)

2. Create the PivotTable using the PivotTable wizard on the Data menu.
In step 2 of the wizard - 'Where is the data you want to use', set the Range to the name defined in step 1 - PivotTableData)

3. In the macro, use the following code to refresh the PivotTable:

Please Login or Register  to view this content.

[LiamF]

many thanks for your help.

regards
Liam