getpivotdata and row total

[djsmarties]

Hi,

i have a question regarding "getpivotdata".

I have a pivot table which looks like this:

count of record days
employee 1-3days 4-6 days 5-7 days grand total
xxx1
xxx2
xxx3

The pivot is refreshed daily and the data for both the row (different employees every day) and the column change depending on the raw data (number of columns changes between 2-6). What i want to do is copy the grand total column out of the pivot table using a macro so i can use it for further calculations . Can I use getpivotdata to get the grand total from each row? And if yes, how?


thanks

 

[Andrew Poulsom]

This will copy the row totals to A1 on Sheet2:

Sub Test()
    With Worksheets("Sheet1").PivotTables(1).DataBodyRange
        .Columns(.Columns.Count).Copy Worksheets("Sheet2").Range("A1")
    End With
End Sub

 

[djsmarties]

Thanks Andrew,

i modified the code to match my data:
Sub copytest()
With Worksheets("pivot").PivotTables(2).DataBodyRange
.Columns(.Columns.Count).Copy Worksheets("test").Range("A1")
End With
End Sub

But i get an error: "Unable to get the PIvotTable property of the Worksheet class"

Am I doing something wrong here? I just copied the code into a macro and then run the macro?

 

[Andrew Poulsom]

Are there at least 2 pivot tables on that worksheet?

 

[djsmarties]

Ah, I thought "PivotTables(1)." is the name of the pivot table.... right, changed it back to 1 and now it works perfectly

Thanks a lot