Recorded Macro Not Including All Rows. 2007

Acton Denton · May 29th 2008

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).

Code

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

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

Robert B · May 29th 2008

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

Hi

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

Code

Sub MakePivot()
Dim myPivot As PivotTable, myField As PivotField, myItem As PivotItem
Dim myRange As Range


Selection.CurrentRegion.Select
Set myRange = Selection
    ActiveWorkbook.Names.Add Name:="Database", RefersTo:=myRange
Set myPivot = ActiveSheet.PivotTableWizard
    Set myField = myPivot.PivotFields("name")
    myField.Orientation = xlDataField
    myField.Function = xlCount' or another function
    myField.Name = "what"    
    Set myField = myPivot.PivotFields("name2")
    myField.Orientation = xlRowField
   Set myField = myPivot.PivotFields("name3")
   myField.Orientation = xlColumnField
    End Sub

Display More

Hope that helps

Robert

Acton Denton · May 29th 2008

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

Thank you I am giving that a try.

AAE · May 30th 2008

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.

Acton Denton · May 30th 2008

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.

**Andy Pope** · May 30th 2008

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.

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

Acton Denton · Jun 5th 2008

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.

Recorded Macro Not Including All Rows. 2007

Participate now!

Share