Link to home
Start Free TrialLog in
Avatar of Natchiket
NatchiketFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Pivot madness Excel 2007

I have an application which creates lots of pivot tables and then copies the numeric values into another worksheet for formatting. it works fine for many iterations and then errors on the line

Set ptl=mwksPT.PivotTables.Add(PivotCache:=ptc,tabledestination:=mwksPT.Cells(mlngPTRow, 2))

with the error Error (1004): Reference is not valid.

mlngPTRow has a value of 47.  And if I look in the worksheet I can see that the row is clear.  However, something seriously bad has happened if you look at the screenshot, down at row 59 something spooky is going on .. another worksheet is manifesting behind 'PivotTables'



If Len(mstrFCol) > 0 Then
    Set ptc = mwkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mwkb.Worksheets(mstrSD).Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeVisible))
Else
    Set ptc = mwkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mwkb.Worksheets(mstrSD).Cells(1, 1).CurrentRegion)
End If
Set ptl = mwksPT.PivotTables.Add(PivotCache:=ptc, tabledestination:=mwksPT.Cells(mlngPTRow, 2))

Open in new window

global.jpg
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Natchiket

ASKER

he he possibly, I'm filtering a table to get a subset at that point
Why not just filter the pivot table?
I might just try that, thanks ...
Oh wait I know why ... because it produces a filter box and that upsets my range handling for copying and pasting the pivot table contents
why does it upset your code? the pivot table has two tablerange properties
Tablerange1 does not include the page fields so should work for you?
gosh I didn't know that .. thanks