changing range for pivot macro

[Tonyk1051]

So i recorded a macro of me creating a pivot and thought that so long as i highlighted the entire spreadsheet and hit insert pivot, the recorded macro would work on any spreadsheet no matter how many lines, i was completely wrong... can some one modify the recorded macro below to work on any spreadsheet? (the code in blue is the error) thanks in advance

[CODE/vba] Sub Macro2()

'

' Macro2 Macro

'



'

Application.CutCopyMode = False

Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Page1!R1C1:R45336C46", Version:=8).CreatePivotTable TableDestination:= _

"Sheet2!R3C1", TableName:="PivotTable2", DefaultVersion:=8

Sheets("Sheet2").Select

Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable2")

.ColumnGrand = True

.HasAutoFormat = True

.DisplayErrorString = False

.DisplayNullString = True

.EnableDrilldown = True

.ErrorString = ""

.MergeLabels = False

.NullString = ""

.PageFieldOrder = 2

.PageFieldWrapCount = 0

.PreserveFormatting = True

.RowGrand = True

.SaveData = True

.PrintTitles = False

.RepeatItemsOnEachPrintedPage = True

.TotalsAnnotation = False

.CompactRowIndent = 1

.InGridDropZones = False

.DisplayFieldCaptions = True

.DisplayMemberPropertyTooltips = False

.DisplayContextTooltips = True

.ShowDrillIndicators = True

.PrintDrillIndicators = False

.AllowMultipleFilters = False

.SortUsingCustomLists = True

.FieldListSortAscending = False

.ShowValuesRow = False

.CalculatedMembersInFilters = False

.RowAxisLayout xlCompactRow

End With

With ActiveSheet.PivotTables("PivotTable2").PivotCache

.RefreshOnFileOpen = False

.MissingItemsLimit = xlMissingItemsDefault

End With

ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels

Range("B8").Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("source_time1")

.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("stage2")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("tf1")

.Orientation = xlRowField

.Position = 3

End With

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

"PivotTable2").PivotFields("repair_id"), "Sum of repair_id", xlSum

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

"PivotTable2").PivotFields("repair_id"), "Sum of repair_id2", xlSum

Range("B23").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of repair_id"). _

Function = xlCount

Range("C30").Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of repair_id2")

.Calculation = xlPercentOfParentRow

.NumberFormat = "0.00%"

End With

Sheets("Sheet2").Select

Sheets("Sheet2").Name = "Piv"

Range("F14").Select

End Sub [/CODE]