Solved

Excel VBA Check if the active sheet has a pivot table on it

Posted on 2015-02-13
3
965 Views
Last Modified: 2016-02-10
Hi

What Excel VBA code would I use to check if the active sheet has a pivot table on it?

Thanks
0
Comment
Question by:murbro
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
if activesheet.pivottables().count>0 then
  'YES, THERE IS ONE.
else
  'NO, THERE ISN'T
End If
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Hi

another way would be something like below. It would tell you in a Pop up message the Sheet name and the Pivot Table name if one or more exists:
Dim shtWS As Worksheet
Dim pvtPT As PivotTable
Dim strMsg As String

strMsg = ""

For Each shtWS In ThisWorkbook.Worksheets
	If shtWS.PivotTables.Count > 0 Then
		strMsg = strMsg & shtWS.Name & vbCr
		For Each pvtPT In shtWS.PivotTables
			strMsg = strMsg & "-" & pvtPT.Name & vbCr
		Next pvtPT
	End If
Next shtWS

MsgBox strMsg

Open in new window

0
 

Author Closing Comment

by:murbro
Comment Utility
Thanks very much
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now