Using Same Macro On Different Workbooks

PorkChops

New Member
Joined
Mar 21, 2011
Messages
3
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

preludeofme

New Member
Joined
Dec 4, 2008
Messages
34
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is it the workbook name or worksheet name that is causing the problem?
 

PorkChops

New Member
Joined
Mar 21, 2011
Messages
3
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.

Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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

New Member
Joined
Mar 21, 2011
Messages
3
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,471
Members
409,884
Latest member
Msinmath
Top