Recorded Macro Not Including All Rows. 2007

  • In Excel 2007 when I manually create a pivot table (and record my actions) the pivot table references all of the information in my data range (70k+ lines). When I run the recorded macro the new pivot table limits the data range to the first 65536 lines (the old limit).

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "Source!R1C1:R1048576C26", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:= _
            "Tables!R9C1", TableName:="PivotTable2", DefaultVersion _

    Any suggestions on how I can force the pivot table from the macro to reference the full data set?

  • Re: Pivot Table Data Limit (version 12 - 2007)


    and welcome to the forum.

    I have always found recorded pivot macros unhelpful and suggest the following code, I am using Excel 2000 and so cannot exceed 65560 lines but I can see no reason why it shouldn't work with the later versions.

    I suggest that you step through the code and enter the fileld names as they occur

    Hope that helps


  • Re: Recorded Macro, When Run, Not Incluing All Rows. 2007

    Is your workbook an Excel 2007 file or is it from an previous version? If it is pre-2007, then possibly Excel is limiting the rows for compatibility with the older version and you need to convert it to the 2007 format.

  • Re: Recorded Macro, When Run, Not Incluing All Rows. 2007

    I created the file in 2007 with an XLSM file type. However the VBA code I am using is from the previous version (2003). Maybe there is something in there forcing it to a version 10 format. I've been experimenting with the above code but it doesn't seem to change the outcome either.

  • Re: Recorded Macro, When Run, Not Incluing All Rows. 2007

    You appear to be using .ADD method, which is xl2003.
    xl2007 uses the CREATE method.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Source!R1C1:R1048576C26", Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:="Table!R3C1", TableName:="PivotTable1", DefaultVersion _


  • Re: Recorded Macro Not Including All Rows. 2007

    Everyone who responded thank you, unfortunately the error was on my source document. I had imported a macro from 2003 which used a max range of 65536 rows and populated formulas based on the number of actual data rows. When the data rows exceeded 65K my formulas were not populated past that point, then when I querried the results of the formulas for the pivot table, it displayed incomplete data. 8(

    So in conclusion there was nothing wrong with my pivot macros.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!