Making a simple Pivot Using VBA

[DigvijaySal]

Hello All,

I created the below VBA for making a pivot run in macros,

It show - Run-time error '5', Invaild Procedure call or argument for the below bold text, can anyone help me figure out my error and how to correct the same, Thanks a Ton!

Sub Pivotmacro()
'
' Pivotmacro Macro
'


'
FR = Cells(Rows.Count, 1).End(xlUp).Row
DataSheet = ActiveSheet.Name
Sheets.Add
NewSheet = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R" & FR & "C45", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=NewSheet & " !R3C1", TableName:="PivotTable4", DefaultVersion _
:=xlPivotTableVersion14
Sheets("NewSheet").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Complaint #"), "Count of Complaint #", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Region")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Complaint Status")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Complaint Status"). _
CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Complaint Status")
.PivotItems("Pre-closure").Visible = False
.PivotItems("Re-open").Visible = False
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Complaint Status"). _
EnableMultiplePageItems = True
End Sub

 

[jimrward]

i may be wrong but when i tried to do this I found the references had to be in R1C1 format throughout and you seem to have mixed them for the sourcedata
i used

intNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
SourceColumn = "!R1C" & Selection.Column & ":R" & intNumRows & "C" & Selection.Column

and then

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    SourceSheet & SourceColumn, Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:=SummarySheet & "!R3C2", TableName:="Pvt" & PivotName, DefaultVersion _
    :=xlPivotTableVersion10