Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table overlap
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pivot table overlap
Timmy wrote:
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 ----------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
"A pivot table report cannot overlap another pivot table report" | Excel Worksheet Functions | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Deleted the pivot table - continue to get the "overlap" error message | Excel Discussion (Misc queries) | |||
A pivot table report cannot overlap another pivot table report. | Excel Discussion (Misc queries) |