Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-02-13
3
Medium Priority
?
2,065 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 24

Accepted Solution

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

Expert Comment

by:Wilder1626
ID: 40607760
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:Murray Brown
ID: 40609671
Thanks very much
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 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