using VBA with getpivotdata on a variable number of rows

[hotwheels]

Hi,

Im making a table with time (0 - 23) on the top and location along the side. Each cell in the table should take data from a pivot table. When I do this manually in Excel I get this:

=GETPIVOTDATA("time round",Sheet5!$A$3,"Station","120 Collins St","group 1",7)

In order to copy this to all the cells in the sheet I replace the "120 Collins St" with it's cell reference. and I do this with the 7 too.

=GETPIVOTDATA("time round",Sheet5!$A$3,"Station",$A2,"group 1",I$1)

I would like to create a macro that will do this for me. However, the macro will have to be able to cope with an undetermined number of rows. Perhaps through using 'lastrow'.

It would be also useful to include the ISERROR function so that zero is displayed in the cell in the case that the pivot table does not have the value corresponding to the table.

Can anyone help me design a macro for this?

[hotwheels]

bump! bump!

[DonkeyOte]

Bumping a post after less than 2 hours over a weekend is not really the "done thing"

If you're using XL2007 as implied

Please Login or Register  to view this content.

modify sheetname etc as necessary

[hotwheels]

Hi,

Thanks a lot for this post.

I have tried it and changed it a little to use if(iserror etc and it works great.

Thanks, legend.

bump