Updating PivotTable, why is the sourcedata limited?

[RustE]

Hi,

I'm working in excel 2007 but I appear to have a problem associated with the row limit from excel 2003. Can't figure it out exactly-

This code snippet works- it updates the pivottable with the order data from LargeModel, which is an input earlier

 ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Workbooks(LargeModel).Worksheets("OrderData").Range("A1:J65536") _
        , Version:=xlPivotTableVersion12)

However, if I try to enter a larger number than 65536, I get a "type mismatch" error. I guess this is the row limit from excel 2003, but I'm using 2007, so I don't know what's causing the error.

Any ideas?

Thanks!!

 

[Andy B]

Have you checked to see if you have more than 65536 rows in your spreadsheet? If not (because it was previously a 2003 workbook) you will have to select Convert from the office button to unlock the extra rows.

 

[RustE]

I checked on it, it is in 2007 and has more than 65536 rows.

I figured out a solution, so I'll post that:

Instead of entering the data range with range("A1:J100000"), this method seems to work. Thanks macro-recorder!
Putting in the extra [ and ] was something I hadn't thought of, but I think it needs them.

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="[" & LargeModel & "]Order Data!C1:C10")