I use this function in my pivot tools to see whether there is any
pivot table to work with.

Function getPivotTable() As PivotTable
Dim ch As ChartObject
On Error Resume Next

Set ch = ActiveChart
If Not ch Is Nothing Then
Set getPivotTable = ActiveChart.PivotLayout.PivotTable ' 1st
will see whether we have an active pivot chart
If Not getPivotTable Is Nothing Then Exit Function
End If

Set getPivotTable = ActiveCell.PivotTable ' 2nd will see whether
there is an active pivot table
If Not getPivotTable Is Nothing Then Exit Function

Set getPivotTable = ActiveSheet.PivotTables(1) ' lets see
whether there is at least one pivot table on the sheet
If Not getPivotTable Is Nothing Then Exit Function

If ActiveSheet.ChartObjects.Count 0 Then ' or an
embedded pivot chart
For Each ch In ActiveSheet.ChartObjects
If ch.Chart.HasPivotFields Then
Set getPivotTable = ch.Chart.PivotLayout.PivotTable
Exit For
Else
Set getPivotTable = Nothing
End If
Next ch
Else ' done our best to find the pivot
table that we can work with
Set getPivotTable = Nothing ' this should trigger an error
message in the caller now.
End If

End Function

' for testing this should work roughly
sub test()
dim pt as pivottable
set pt=getpivottable()
if pt is nothing then
msgbox "Goto sheet with a pivot table and try again!"
exit sub
end if
end sub