Pivot Tables

[Clarissa]

I recorded this macro, and it works fine with the file I recorded it on. A
new file is generated each month with the same field names, but just more
rows.

I tried to run it with this month's file and got an error on the first set
of instructions, does the error have to do with the Pivot table name or does
it fail because there are more records?

here is the code:

Sub Pivots()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"YTD!R1C1:R450C65").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable13", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable13").NullString = "0"
ActiveSheet.PivotTables("PivotTable13").AddDataField
ActiveSheet.PivotTables( _
"PivotTable13").PivotFields("EMPLID"), "Count of EMPLID", xlCount
With ActiveSheet.PivotTables("PivotTable13").PivotFields("Location")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable13").PivotFields("Sex")
.Orientation = xlRowField
.Position = 2
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable13").PivotFields("Location"). _
Subtotals = Array(False, False, False, False, False, False, False,
False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable13").PivotSelect "", xlDataAndLabel,
True
Selection.Copy
Workbooks.Open Filename:= _
"W:\HR Reporting\Reporting Toolkit\Automated\Scorecard Pivots.xls"
Range("F1").Select
ActiveSheet.Paste
Columns("A:E").Select
Range("E1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Windows("Hires.xls").Activate
Range("B4").Select
ActiveSheet.PivotTables("PivotTable13").PivotFields("Sex").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable13").PivotFields("Eth")
.Orientation = xlRowField
.Position = 2
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable13").PivotSelect "", xlDataAndLabel,
True
Selection.Copy
Windows("Scorecard Pivots.xls").Activate
Sheets("HIR_Eth").Select
Range("F1").Select
ActiveSheet.Paste
Columns("A:E").Select
Range("E1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
End Sub