VBA pivot table creation automation and relative reference for worksheet

[cgapperi]

I am trying to create an macro that would auto-create a pivot table. I can record the macro and make it work perfectly on the same file. However, the name of the file I would like to run this macro on changes every day. There must be a way to make this statement be relative and not require the name of the sheet:

[B]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/B]
[B]       "Weekly_Sales_Re-10-26-09!R1C1:R1197C9", Version:=xlPivotTableVersion12). _[/B]
[B]       CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable2" _[/B]
[B]       , DefaultVersion:=xlPivotTableVersion12[/B]

I need to change the SourceData to be a relative reference to the sheet I am running it on and to have it select the appropriate range, which will likely change on any given worksheet.

Any help on this would be appreciated. The full macro looks like so:

Sub create_wsr_pivot()
'
' create_wsr_pivot Macro
'
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Weekly_Sales_Re-10-26-09!R1C1:R1197C9", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable2" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Corridor")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("title")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("rep_date")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("week"), "Sum of week", xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of week")
        .Caption = "Weeks Reporting"
        .Function = xlCount
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("2008"), "Sum of 2008", xlSum
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of 2008").Caption = _
        "2008 Sales"
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("2009"), "Sum of 2009", xlSum
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of 2009").Caption = _
        "2009 Sales"
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("diff"), "Sum of diff", xlSum
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of diff").Caption = _
        "Sales Change"
    ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "% Change", _
        "=diff/'2008'", True
    ActiveSheet.PivotTables("PivotTable2").PivotFields("% Change").Orientation = _
        xlDataField
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of % Change")
        .Caption = "Ave % Change"
        .Function = xlAverage
    End With
    Range("A5").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Corridor").ShowDetail = _
        False
End Sub