Avatar of Natchiket
Flag 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))
    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

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

he he possibly, I'm filtering a table to get a subset at that point
Rory Archibald

Why not just filter the pivot table?

I might just try that, thanks ...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

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
Rory Archibald

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.