VBA Build Pivot Table Help

[mrazexcel]

Good Evening,

A few months ago I started learning VBA and have had a great time learning. Today I need some assistance on my pivot table building.

My macro works fine when I use it, Excel 2013, but when an associate did a quick test it errored out on them, he uses Excel 2007. The associate was in a rush but I was able to do a quick debug and it yellowed the section in my code that starts building the pivot table (highlighted in red). My macro does a lot of pre work to the data before building the pivot table so the below code is about 1/3 down in my macro code:


'Create Tab For Pivot View'
Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Select
Sheets("Sheet2").name = "Pivot"


'Build Pivot Table'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R1048576C14", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivot!R5C5", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

Sheets("Pivot").Select
Cells(5, 5).Select
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Unit Manager")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Associate Manager")
.Orientation = xlRowField
.Position = 2
End With

I used the macro recorder to record the above code. Am I having issues with other people running my macro because I don't have all that other code I see in peoples macros like:

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

Is all the above code necessary? If it helps my data set is column A - M, and 200 rows. Thx for any help you can provide.

[MarvinP]

Hi mrazexcel,

Look at the maximum number of rows for Excel 2007 vs your 2016.
I think 2007 only allows 1 million rows and your code tries to address more than that...

I'd suggest you study up on Dynamic Named Ranges and use the Name Range for your pivot array. Then it might work on both machines.
http://www.ozgrid.com/Excel/DynamicRanges.htm

[mrazexcel]

Marvin thats a great call out on the max row difference between excels. When i get back to work in the morning I will give that a shot. Thx

[humdingaling]

get rid of this

Please Login or Register  to view this content.

also read this guide to help you along with VBA pivot tables
http://www.thespreadsheetguru.com/bl...l-pivot-tables

[humdingaling]

Oh, get rid of this one too

Please Login or Register  to view this content.

[mrazexcel]

Thx humdingaling, I've googled why you recommend getting rid of them and see the different versions of excel 12, 14, 15. So you believe the below code will be fully functional:

'Build Pivot Table'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R1048576C14").CreatePivotTable _
TableDestination:="Pivot!R5C5", TableName:="PivotTable1"

[MarvinP]

I can't wait to see if his solution works. I'm still guessing the R1048576 is too big.

Please let us know when you test it.

[mrazexcel]

Roger that, will reply tomorrow on my findings.

[humdingaling]

Excel 2007 Worksheet size
1,048,576 rows by 16,384 columns

should be fine
if that is an issue just dial back the number
i'm pretty sure your not actually using 1048576 rows of data

[mrazexcel]

Hi, an update:
First I tried adjusting the range to a smaller size. Macro still threw an error. Second I tried removing the verison15 in the code and bingo that did the trick. Thx to both of you for your input.

[humdingaling]

not a problem

just for learning...you really shouldnt allocate your data source to be more than you require
if for whatever reason you cannot be exact....a reasonable guesstimate of maximum number of rows should be used
ie 10k or 100k...if you are actually requiring 1 million+ rows...then you should probably be looking at a database

the link i posted on #4 i use quite often to refresh my memory on syntax for pivot tables
its well worth a study/read