Trying to create pivot using Macro - doesn't work?!?

[syim]

I am trying to create a pivot table using macro. In order to accommodate for changes in data, I used dynamic range.
See below for the step I took:
1) Create dynamic range using offset formula (=OFFSET('CommTest Booking Selections Gre'!$A$2,0,0,COUNTA('CommTest Booking Selections Gre'!$A:$A),21)
2) Record Macro
3) While recording: created pivot table using dynamicrange, added a new column next to the pivot to get additional data (if formula), and formatted.
4) Stop recording and delete sheet 1(where pivot was created) and used the shortcut to re-create the pivot

Once I do step 4, I get an error message: Run-time error '5': Invalid procedure call or argument and highlights the area when I click "debug":

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DynamicRange", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable11", DefaultVersion _
:=xlPivotTableVersion14

Please see below for the entire VBA codes:

Also note that, I already saved my file in .xlsm format so macro should work. Could anyone tell me what I am doing wrong?


Sub Over50KbyPO()
'
' Over50KbyPO Macro
' Total order value >$50K by PO
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DynamicRange", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable11", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable11").PivotFields("Customer Po Number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable11").AddDataField ActiveSheet.PivotTables( _
"PivotTable11").PivotFields("Cc Extended Selling Price"), _
"Sum of Cc Extended Selling Price", xlSum
Columns("B:B").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("C3").Select
ActiveCell.FormulaR1C1 = "$50K"
Range("C3").Select
ActiveCell.FormulaR1C1 = ">$50K by PO"
Range("C4").Select
Columns("C:C").ColumnWidth = 11.89
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>50000,""Y"",""N"")"
Range("C4").Select
Selection.AutoFill Destination:=Range("C4:C3157")
Range("C4:C3157").Select
Range("C9").Select
Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = ""
Range("E3138").Select
End Sub

[Richard Buttrey]

Hi, and welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)