Help with macro

[INOPOOL]

I am trying to create a macro for a pivot table i am creating, but it gives me an error message whenver i try to run the macro. This is my code:

Sheets.Add
ActiveWorkbook.Worksheets("Summary").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable3" _
, DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Cost_Center_(Division)")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Department")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Employee_Number"), "Count of Employee_Number", _
xlCount
End Sub

 

[jmthompson]

What's the error?

 

[texasalynn]

when you create a macro to run pivot tables it is usually best to create a more unique name

PT3 instead of the excel generated PivotTable3

 

[INOPOOL]

Error message is Run-time error '5':

Invalid procedure call or argument.

This is the new code:
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Employee Roster- Active Employe!R1C1:R2547C5", Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Employee_Number"), "Count of Employee_Number", _
xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Cost_Center_(Division)")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Department")
.Orientation = xlColumnField
.Position = 1
End With
End Sub

 

[texasalynn]

in the vb editor do a replace
find: PivotTable1
replace PT1

what line does it give you the error?

 

[INOPOOL]

Did the replacement...here goes the code:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
"Employee Roster- Active Employe!R1C1:R2547C5", Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PT1", DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PT1").AddDataField ActiveSheet.PivotTables( _
"PT1").PivotFields("Employee_Number"), "Count of Employee_Number", _
xlCount
With ActiveSheet.PivotTables("PT1").PivotFields( _
"Cost_Center_(Division)")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PT1").PivotFields("Department")
.Orientation = xlColumnField
.Position = 1
End With
End Sub

--When I hit the debug in the error pop up box, it highlights the lines that I highlighted in red...maybe those are the error lines???

 

[texasalynn]

do you have a sheet by this name?
"Employee Roster- Active Employe"