Used Range and Source data of a Pivot Table

[jh]

I am trying to write a function to ascertain if a worksheet solely
contains a pivot table based on information from elsewhere in that
workbook, E.g. a worksheet containing data formula would return False,
A worksheet containg data and a pivot would return false, a worksheet
containing a pivot of external data would return false, but a
worksheet containing a pivot of data within the workbook would return
true. I'm not sure of any direct way to do this, but am thinking on
the lines if the used range of the sheet matched the range used by the
pivot and the source data did not reference another file that would
give me the correct result, however I can't find any properties for a
PivotTable object. I get as far as wks.PivotTables(1). then help
runs out of help! Could anyone point me in the right direction please?
Thanks J. (Win7/Excel 2007)

 

[Dave Peterson]

First, the .usedrange of a worksheet may not be what you expect. If you've
pivot'ed the table, excel could be showing a larger .usedrange than you would
expect.

But if you can reset the .usedrange, you may find that something like this
works:

Option Explicit
Sub testme()

Dim DummyRng As Range
Dim wks As Worksheet
Dim PTRng As Range

Set wks = Worksheets("sheet5")

With wks
If .PivotTables.Count <> 1 Then
MsgBox "Not exactly one pt on this sheet"
Exit Sub
End If

'try to reset the used range
Set DummyRng = .UsedRange

Set PTRng = .PivotTables(1).TableRange2
End With

If DummyRng.Address = PTRng.Address Then
MsgBox "it looks like just a pt"
Else
MsgBox "It doesn't look like just a pt"
End If

End Sub

Sometimes, it's as simple as doing that "set dummyrng = .usedrange". Sometimes,
that doesn't work.

Visit Debra Dalgleish's site:
http://contextures.com/xlfaqApp.html#Unused

For other ways to try to reset that used range.