Finding Overlapping Pivot Tables

I have a large file with many tabs and even more pivot tables.  The file works for my needs, but I've developed a problem where there seems to be a couple of overlapping pivot tables someplace.  I'm getting the popup "A pivot table report cannot overlap another pivot table report."  I've searched each sheet, and can't seem to find the problem.  Checked to make sure there weren't any hidden sheets.  I've searched the web for this, and seen the guidance around preventing this from happening, hidden columns, hidden sheets, etc. and understand these issue.  I'm just wondering if there's a quick way of finding any sheet with multiple pivot tables.  Maybe a macro that generates a list of pivots by sheet?  Doesn't seem too tough, but I'm a little rusty in VB programming, so thought I'd ask here first.

Thanks in advance for the help.

Answer
Answer

I have a large file with many tabs and even more pivot tables.  The file works for my needs, but I've developed a problem where there seems to be a couple of overlapping pivot tables someplace.  I'm getting the popup "A pivot table report cannot overlap another pivot table report."  I've searched each sheet, and can't seem to find the problem.  Checked to make sure there weren't any hidden sheets.  I've searched the web for this, and seen the guidance around preventing this from happening, hidden columns, hidden sheets, etc. and understand these issue.  I'm just wondering if there's a quick way of finding any sheet with multiple pivot tables.  Maybe a macro that generates a list of pivots by sheet?  Doesn't seem too tough, but I'm a little rusty in VB programming, so thought I'd ask here first.

Thanks in advance for the help.

Try this macro get a list of pivot tables for each worksheet that has more than one pivot table:

Sub list_all_pivottables_on_worksheets_with_more_than_one_pivotable()
  For Each ws In Worksheets
    If ws.PivotTables.Count > 1 Then
      For Each pt In ws.PivotTables
         Debug.Print ws.Name & ":" & pt.Name
      Next pt
    End If
  Next ws
End Sub

257 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 23, 2020 Views 70,202 Applies to: