Recorded Macro

[mharri]

Hi

I've recorded the following Macro in a workbook called SCSGroupageT which is obviously referenced in the code. How do i need to amend the code so that it will run in any workbook?


Thanks

Sub Testvba()
'
' Testvba Macro
'


'
    Range("A1:I2656").Select
    Range("C4").Activate
    Workbooks("SCSGroupageT.xlsx").Connections.Add2 _
        "WorksheetConnection_SCSGroupageT!$A$1:$I$2656", "", _
        "WORKSHEET;C:\Users\mharri\Desktop\[SCSGroupageT.xlsx]SCSGroupageT", _
        "SCSGroupageT!$A$1:$I$2656", 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_SCSGroupageT!$A$1:$I$2656"), _
        Version:=6).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:= _
        "PivotTable1", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").CubeFields( _
        "[Range].[Groupage Department]")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").CubeFields("[Range].[grp route]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
        "[Range].[Groupage Number]", xlCount, "Count of Groupage Number"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Count of Groupage Number]"), _
        "Count of Groupage Number"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Measures].[Count of Groupage Number]")
        .Caption = "Distinct Count of Groupage Number"
        .Function = xlDistinctCount
    End With
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
        "[Range].[Job Number]", xlCount, "Count of Job Number"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Count of Job Number]"), _
        "Count of Job Number"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Measures].[Count of Job Number]")
        .Caption = "Distinct Count of Job Number"
        .Function = xlDistinctCount
    End With
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure "[Range].[Pieces]" _
        , xlSum, "Sum of Pieces"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Sum of Pieces]"), "Sum of Pieces"
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure _
        "[Range].[Weight Kgs]", xlSum, "Sum of Weight Kgs"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Sum of Weight Kgs]"), _
        "Sum of Weight Kgs"
    ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure "[Range].[Cube]", _
        xlSum, "Sum of Cube"
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").CubeFields("[Measures].[Sum of Cube]"), "Sum of Cube"
    Range("E4:E24").Select
    Selection.Style = "Comma"
    Range("F4:F24").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0.000_-;-* #,##0.000_-;_-* ""-""??_-;_-@_-"
    Selection.NumberFormat = "_-* #,##0.0000_-;-* #,##0.0000_-;_-* ""-""??_-;_-@_-"
End Sub