Pivottable Location Property

[Ziggy12]

In Excel 2007. How do I step through pivot tables on one worksheet and get the locaton of each.
Tried this but it return an error at XXXX
Sub CreatePictures()
Dim Pt As PivotTable
Dim Ws As Worksheet
Dim Loc(20) As String

Set Ws = Sheets("Summary pivots by DRG")
Ws.Activate

test = Ws.PivotTables.Count
x = 0
For Each Pt In Ws.PivotTables
x = x + 1
Loc(x) = Pt.Location 'XXXX
Next

End Sub

cheers
ziggy

 

[dk]

Hi,

What do you mean by "location"? If you mean the cell address then you could use something like this. This returns where the pivot table body starts. You could also use PageRange object if you want to refer to where the Page Fields are located.


HTH
DK

Sub CreatePictures()
Dim Pt As PivotTable
Dim Ws As Worksheet
Dim Loc(20) As String

Set Ws = Sheets("Summary pivots by DRG")
Ws.Activate

test = Ws.PivotTables.Count
x = 0
For Each Pt In Ws.PivotTables
x = x + 1
Loc(x) = Pt.DataLabelRange.Cells(1, 1).Address
Next

End Sub

 

[Ziggy12]

Thanks for that
The location property is in xl 2007

PivotTable.Location Property
Gets or sets a String that represents the top-left cell in the body of the specified PivotTable. Read/write.
Version Information
Version Added: Excel 2007

Syntax

expression.Location

expression A variable that represents a PivotTable object.

Maybe it doesn't work on all versions.

cheers
ziggy

 

[Ziggy12]

Thanks for the inspiration I found tablerange2 after a bit more digging around.

Sub PictPaster()
Dim Pt As PivotTable
Dim Ws As Worksheet

Set Ws = Sheets("Summary pivots by DRG")
Ws.Activate

For Each Pt In Ws.PivotTables
Range(Pt.PageRange.Address).PivotTable.TableRange2.Select
Selection.CopyPicture
Cells(Pt.PageRange.Row + 50, Pt.PageRange.Column).Select
Selection.PasteSpecial
Next
End Sub

cheers
ziggy

 

[dk]

OK, glad you got it sorted. I just checked an it looks like the Location property was added in 2007. Seems a little un-necessary when this information was already available using the Address of one of the various range objects in the pivot table.

Cheers
DK