Pivot Table VBA

[barmacost]

I want a simple macro to copy all data below column headers in a pivot table and paste to another sheet. I've tried multiple methods for selecting the data to copy and it always gives me the same error. "run-time error 438, Object doesn't support this property or method." Below is the code. Any ideas why I keep getting this error?


Sheets("TaskStatus").Pivotables("TaskStatus").DataBodyRange.Select

Selection.Copy

Sheets("Change_Data").Select

Range("A5").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

 

[Hui]

Barmacost


Not sure exactly why you get that error, and I'm not sure exactly how to select an Individual Pivot Table by name, I've never tried to.

However, assuming you only have 1 Pivot Table on the TaskStatus page

This will do the job.

[pre]

Sub CopyPivot()
Dim pvt As PivotTable
Dim rng As Range
Dim rowOff As Long, colOff As Long

rowOff = 4 'for A5
colOff = 0 'for A5

For Each pvt In ActiveSheet.PivotTables

With pvt
.ColumnGrand = True
.RowGrand = True
End With

Set rng = pvt.DataBodyRange

Worksheets("Change_Data").Select
' Use this for Data and Totals
Range(Cells(1 + rowOff, 1 + colOff), Cells(rng.Rows.Count + rowOff, rng.Columns.Count + colOff)).Value = rng.Value

' Use this for Data and Totals
'Range(Cells(1+RowOff, 1+ColOff), Cells(rng.Rows.Count+RowOff, rng.Columns.Count+ColOff)).Value = rng.Value
Next pvt

End Sub

[/pre]