VBA to create a Pivot Table

Can somebody help with the code below. I am looking at acheiving the following:
1. Delete the current pivot table in 'output' sheet
2. selecting data for new pivot table (this is where the problem is!!!!)
3. Creating new pivot table in output sheet

The problem is the data for the pivot table varies in length so the selection of data needs to allow for this. This is what I am trying to do with the sourcedata criteria by using End(xldown) and End(xltoright) operators. Can anybody tell me where I'm going wrong? and how I can pick up the data range for the pivot table. Many thanks in advance,

Jim.

Sub Pivot_Report()
'
' Pivot_Report Macro
' Delete previous piivot table
Sheets("Output").Select
Cells.Select
Selection.Delete Shift:=xlUp

'select data for replacement pivot table
Sheets("Report").Select
Range("A4").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"A4:I28", TableDestination:=" '[Customer Accounts - Bad Debts v1.xls]Output'!R1C1", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight))", TableDestination:=" '[Customer Accounts - Bad Debts v1.xls]Output'!R1C1", TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Customer Ref", "Customer Name", "Data"), PageFields:="Status"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("< 90 Days")
.Orientation = xlDataField
.Name = "Sum of < 90 Days"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("91 - 180 days")
.Orientation = xlDataField
.Name = "Sum of 91 - 180 days"
.Position = 2
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("181 - 360 days")
.Orientation = xlDataField
.Name = "Sum of 181 - 360 days"
.Position = 3
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("> 360 days")
.Orientation = xlDataField
.Name = "Sum of > 360 days"
.Position = 4
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total")
.Orientation = xlDataField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Total > 180").Orientation _
= xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Data[All]", xlLabelOnly
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Customer Ref'[All;Total]" _
, xlDataAndLabel
Selection.Delete
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlOrigin
End Sub