using VBA with getpivotdata on a variable number of rows

**hotwheels** · 10-24-2010, 08:17 PM

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** · 10-24-2010, 10:10 PM

bump! bump!

**DonkeyOte** · 10-25-2010, 03:27 AM

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

If you're using XL2007 as implied

Sub Example()
    With Sheets("sheetname")
        With .Range(.Cells(2,"A"),Cells(.Rows.Count,"A").End(xlUp)).Offset(,1).Resize(,24)
            .FormulaR1C1 = "=IFERROR(GETPIVOTDATA(""timeround"",Sheet5!R3C1,""Station"",RC1,""group 1"",R1C),0)"
        End With
    End With
End Sub

modify sheetname etc as necessary