Run time error when run recorded macro in excel

Question

I did recorded some macros in excel and it was working perfectly till I did record a macro to creating a pivot table but after that I was facing "Run time error '5' invalid procedure call or argument" when I run this macro ! I tried to change the extension of the file to .xlsm but didn't work !!

Im not very expert in VB
the following is the code

Sub pivot()
'
' pivot Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "dynamictable", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="All Wanting!R10C11", TableName:="PivotTable6", _
    DefaultVersion:=xlPivotTableVersion14

Sheets("All Wanting").Select
Cells(10, 11).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Date")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
    "PivotTable6").PivotFields("Date"), "Count of Date", xlCount
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Type")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
    "PivotTable6").PivotFields("Date"), "Count of Date2", xlCount
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Count of Date2")
    .Caption = "Sum of Date2"
    .Function = xlSum
End With
Range("K8").Select

End Sub

when i do debug I see the error at the first 4 lines !

any idea ?!

Could you explain what the code is trying to do in words? ie. create a pivot table from data on sheet1, sort by Date in acending order, change field to show summed value instead of count etc... — Calico

dcp dcp · Accepted Answer · 2014-03-26T12:57:27

In Excel, you can press ALT-F11 to get to the VBA window. Then, you can use the Debug->Compile VBA Project command from the menu bar to find out your errors.

Also, be sure to put "Option Explicit" at the top of each of your VBA source files, as this will ensure that you have variables declared before using them, etc. and will help you find potential code problems.

Also, you can set a breakpoint on the Sub pivot() and step through your code in the debugger to find out the runtime error if the above suggestions don't help.

Run time error when run recorded macro in excel

1 Answers