Timmy said:
I have a workbook with many, many pivot tables. When I refresh all pivot
tables it gives me a pivot overlap error. how can I find which one is causing
the problem. all of the pivot tables are limited to a set number of items
they can show.
Thanks in advance
Tim
I will take a stab. Probably not ideal...
The problems with solving this easily, as I see it, are two-fold: (1)
Excel does not identify which PTs are at fault (hence your post), and
(2), refreshing PTs that cause an overlap in code does not generate an
error (tested with Excel 2003).
So how to find overlapping pivot tables?
First I had to contrive a situation where overlapping PTs could exist.
The only way I could think of was to create two PTs on the same sheet
that peacefully coexist close together (the wiz will not allow you to
create overlapping PTs). Then I added some values to one "killer PT"
that would cause a fault on refresh. Set the killer PT to refresh on
open, close the workbook without refreshing, and reopen. Error message
about "cannot overlap" ensues. But where is the problem?
My attack is use VBA to:
- refresh all the PTs
I noticed here that a killer PT is truncated. It only expands as far as
it can go without overwriting an adjacent PT. So now, identify the PT
ranges that have a common border:
- loop though each worksheet
- add the range address of each PT to a collection
- loop though the collection
- test every combination of ranges to see if they are adjacent
- a "potential collision" is identified if so
This is not heavily tested, and I am not impressed with my kludgy method
of testing for adjacent ranges, but this does work in my limited test case.
Paste this code in a standard module in your workbook and run the Sub
(sorry about the line wrap):
' begin code ---------------------------------------------------
Sub FindPossiblePivotTableCollision()
Dim WKS As Worksheet
Dim PC As PivotCache
Dim PT As PivotTable
Dim AddressCollection As Collection
Dim i As Long
Dim j As Long
For Each PC In ActiveWorkbook.PivotCaches
PC.Refresh
Next
For Each WKS In ActiveWorkbook.Worksheets
Set AddressCollection = New Collection
For Each PT In WKS.PivotTables
AddressCollection.Add PT.TableRange1.Address
Next PT
If AddressCollection.Count > 1 Then
For i = 1 To AddressCollection.Count - 1
For j = i + 1 To AddressCollection.Count
If AreAdjacent(Range(AddressCollection(i)),
Range(AddressCollection(j))) Then
Debug.Print "Possible collision in worksheet " & _
WKS.Name & " in ranges " & _
AddressCollection(i) & "," &
AddressCollection(j)
End If
Next j
Next i
End If
Next WKS
End Sub
Function AreAdjacent(Range1 As Range, Range2 As Range) As Boolean
Dim T1 As Single
Dim T2 As Single
Dim H1 As Single
Dim H2 As Single
Dim L1 As Single
Dim L2 As Single
Dim W1 As Single
Dim W2 As Single
T1 = Range1.Top
T2 = Range2.Top
H1 = Range1.Height
H2 = Range2.Height
L1 = Range1.Left
L2 = Range2.Left
W1 = Range1.Width
W2 = Range2.Width
If T1 + H1 = T2 Or _
T2 + H2 = T1 Or _
L1 + W1 = L2 Or _
L2 + W2 = L1 Then
AreAdjacent = True
Else
AreAdjacent = False
End If
End Function
' end code -----------------------------------------------------
