Hi Everyon, I have a Macro that creates a pivot table. The Macro runs very fast on the first work sheet. When I go to the second work sheet and run the macro again, it still runs fast but is diffently slower. Each time I select another work sheet and run the Macro, it continues to slow down. After 10 work sheets, I find that I can create the pivot table faster if I do it myself. I don't know where the slow down is happeing. If it was the Macro or VBA code, wouldn't it be slow all the time? I added the code below for reference

Sub Pivot_Table()

' Pivot Table


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Columns("A:G").CurrentRegion).CreatePivotTable TableDestination:=ActiveSheet.Cells(2, 10), TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Number Returned"), _
"Count of Returned", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Number"), _
"Count of Number", xlCount
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rate (%)"), "Count of Rate (%)", xlCount
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Returned")
.Caption = "Sum of Number"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Number")
.Caption = "Sum of Number"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Rate (%)")
.Caption = "Average of Rate (%)"
.Function = xlAverage
.NumberFormat = "0.00"
End With
Range("I2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("H2").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").AutoSort _
xlDescending, "Sum of Number"

Range("O3").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""J""&SUMPRODUCT(MAX((ROW(C[-5]))*(C[-5]<>""""))-2))"
Range("S3").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""M""&SUMPRODUCT(MAX((ROW(C[-6]))*(C[-6]<>""""))-2))"
Range("O4").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""J""&SUMPRODUCT(MAX((ROW(C[-5]))*(C[-5]<>""""))-1))"
Range("S4").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""M""&SUMPRODUCT(MAX((ROW(C[-6]))*(C[-6]<>""""))-1))"
Range("O5").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""J""&SUMPRODUCT(MAX((ROW(C[-5]))*(C[-5]<>""""))))"
Range("S5").Select
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""M""&SUMPRODUCT(MAX((ROW(C[-6]))*(C[-6]<>""""))))"
Range("O7").Select
ActiveCell.FormulaR1C1 = "Site rate"
Range("S7").Select
ActiveCell.FormulaR1C1 = "=R[-3]C/R[-4]C"
Range("S7").Select
Selection.NumberFormat = "0.00%"


End Sub