views:

3955

answers:

2

I've been having a major issue... well maybe not major, but I've been trying to figure this out since yesterday lunchtime.
I have the following code:

Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "Data!R7C1:R5000C40").CreatePivotTable TableDestination:= _
    "'[Master-File.xls]Analyse'!R20C14", TableName:="certain_view", DefaultVersion _
->   :=xlPivotTableVersion10

The runtime error is in the line with the arrow, but the entire bit quoted above highlights yellow.

Application-defined or object-defined error

I am using Excel 2003, VBA. I have these few lines of script in my code five times, and only this bit fails every time. I am trying to create the fifth PivotTable with these lines.

I think it might have something to do with the amount of data in memory... yet the Application.Cutopymode = False doesn't fix anyhting.

A: 

Just guessing ... Is ActiveWorkbook still ok? Have you tried using a named workbook?

Remou
My bad. Due to a variable dataset, and an error with PivotItems being visible while I don't want them to be, two tables overlapped eachother. That threw the error. Thanks anyway!
Skunk
It may be worth posting this as a possible solution to this problem. YesNo?
Remou
Yes... good point.
Skunk
A: 

Fixed it (earlier, Remou pointed out that posting the answer is a good thing).
I messed up a bit.

My data was dynamic, on every reload another set. In my testing set, everything went awesomely fine. Then, as I loaded a new set, values changed, labels were added, others were left out... one of my pivot tables became a lot larger. A second one was programmed to be created in a certain cell, but that cell was now part of that larger table.

Not a very clear error message, but 'aight, it's fixed.

Moral: try to think of every possibility when assigning tables to worksheets.

Skunk