Macro's errors

[Oakey]

Is it possible to create a Macro that will sort some data, then used this sorted Data to create a Pivot table, then using the results of the pivot table, pasting the results as values then creating another pivot table? All this is done on the same sheet.

I have tried to create this macro and run I but I get an error message straight after sorting the data, before the pivot table is run

Error message is

Run time error 5
invalid procedure call or argument

Macro is below (Text in bold is where the problem starts)

Sub MD()
'
' MD Macro
'

'
Range("A:A,B:B,D:G,I:I").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:D").Select
ActiveWorkbook.Worksheets("MD Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MD Data").Sort.SortFields.Add2 Key:=Range( _
"B2:B4094"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("MD Data").Sort
.SetRange Range("A1:D4094")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"MD Data!R1C1:R1048576C4", Version:=6).CreatePivotTable TableDestination:= _
"MD Data!R1C6", TableName:="PivotTable1", DefaultVersion:=6
Sheets("MD Data").Select
Cells(1, 6).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Order number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Count of Amount", xlCount
Range("H1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],C[-7]:C[-4],4,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H1278")
Range("H2:H1278").Select
Columns("F:H").Select
Selection.Copy
Range("J1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"MD Data!R1C10:R1048576C12", Version:=6).CreatePivotTable TableDestination _
:="MD Data!R1C14", TableName:="PivotTable2", DefaultVersion:=6
Sheets("MD Data").Select
Cells(1, 14).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Count of Amount"), "Sum of Count of Amount", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Row Labels"), "Count of Row Labels", xlCount
End Sub

 

[Norie]

What's the error message?

Also, why sort before creating the pivot?

You should be able to sort the pivot itself.

 

[Oakey]

Error message is

Run time error 5
invalid procedure call or argument

Not sure what the error means.

Before i was sorting before the pivot table as i only wanted certain information, but now thinking about it i may not have to sort.