CODEWITHSUNDEEP
excelruntime-errorpivot-table
Kablam
Kablam

Reputation: 2591

Runtime error 1004: Application-defined or object-defined error

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.

Upvotes: 1

Views: 8776

Answers (2)

Kablam
Kablam

Reputation: 2591

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.

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

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

Upvotes: 0

Related Questions