Sorting Issue

[zsjoy]

I have a workbook with several worksheets. I add new sheets to the workbook often. The sheets contain the same type of data, but with different dates, amounts, vendors, etc. After I enter the new data, I will need to sort according to certain criteria. I am fine with this step. What is aggravating me in 2007 version is that I have to redefine the Sort By and the Then By for each worksheet. In the 2003 version, this defaulted for the woorkbook that I would be using. Is there a way to save the sort procedure so I don't have to redefine how to sort each time? I tried to record a macro (a process I am not very familiar with), but it appears it only works on the worksheet that it was created for. Also, the number of rows changes with each worksheet.

 

[jasonb75]

Your recorded macro should look something like

Sub Macro5()
'
' Macro5 Macro
'
'
    ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range( _
        "C2:C1902"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Add Key:=Range( _
        "E2:E1902"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Summary").Sort
        .SetRange Range("A1:E1902")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Please create a backup copy of your workbook before trying this.

Using your own recorded macro, not this one, replace each occurence of "ActiveWorkbook.Worksheets("your sheet name")" with "ActiveSheet", this should then allow you to use it with any sheet.

Note that if the number of rows in your sheets varies then some additional editing will be needed to allow for this.

Hope this helps

 

[zsjoy]

Jason - You are a life saver! Thanks so much!!!