Pivot madness Excel 2007

Natchiket used Ask the Experts™
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))
    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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
You can't build a pivot table off a discontiguous range - it has to be one solid table, so perhaps  your reference to specialcells(xlcelltypevisible) might be the root of the problem?
he he possibly, I'm filtering a table to get a subset at that point
Most Valuable Expert 2011
Top Expert 2011

Why not just filter the pivot table?
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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
Most Valuable Expert 2011
Top Expert 2011

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial