Using Same Macro On Different Workbooks

[PorkChops]

I am trying to create a macro to sort and subtotal spreadsheets that my company gets online. They are all of the same format, same width but some have more rows of information than others. The workbook name is just random numbers each time I open a file, and it's causing me a bit of trouble. I recorded a macro on one document, but am unable to get it to run on any others. Here is what it showed up as:

Anyone think they can help me figure out how to get this to work in other workbooks?

 

[preludeofme]

you will probably want to create a different macro that will open those files automatically, extract the information, and then run the macro. So have a main processing spreadsheet.

also whatever you attached didn't work, you'll probably want to attach the code so that we can see what you are working with

 

[JoeMo]

Is it the workbook name or worksheet name that is causing the problem?

 

[PorkChops]

Oops, sorry, I didn't see the option to quote the coding. Also, it's both the workbook name and worksheet name. They are the same thing on one file, but just change every time I open a file.

Sub CostSharing()
'
' CostSharing Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Rows("1:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:A4").Select
    ActiveWorkbook.Worksheets("51624.552").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("51624.552").Sort.SortFields.Add Key:=Range( _
        "A5:A125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("51624.552").Sort.SortFields.Add Key:=Range( _
        "C5:C125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("51624.552").Sort
        .SetRange Range("A4:N125")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 9), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=4
    ActiveWindow.Zoom = 90
End Sub

 

[JoeMo]

Here's an untested revision that should work for any workbook that you have active. Be sure to put this code in your Personal.XLSB workbook.

Sub CostSharing()
' CostSharing Macro
' Keyboard Shortcut: Ctrl+Shift+D
Dim ws As Worksheet
Set ws = ActiveWorkbook.ActiveSheet
    Rows("1:4").Select
    Range("A4").Activate
    Selection.Delete Shift:=xlUp
    Range("A1:A4").Select
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range( _
        "A5:A125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ws.Sort.SortFields.Add Key:=Range( _
        "C5:C125"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ws.Sort
        .SetRange Range("A4:N125")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 9), _
        Replace:=False, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=4
    ActiveWindow.Zoom = 90
End Sub

 

[PorkChops]

That coding seems to work, except it's not sorting properly. I think it's because the range isn't always the same. It always starts in A5/C5, but is longer/shorter than 125.

 

[JoeMo]

You can sort on a single cell key. The sorting will be done on the current region of that cell. So Change A5:A125 to A5 and C5:C125 to C5.