Sorting code that will work on multaple sheets

[Whitoja]

I have used a recorded macro to alphabetize a column of names (range [A1:A100], A to Z) on a single Excel worksheet. I am about to add additional worksheets having columns of names in the same range. I don't know how to code this function so that it will work on all the worksheet, not all at the same time but separately. What I am using now is associated with a single worksheet and will not work with the added new sheets.

Using:
Windows 7
Excel 2007

Any help would be appreciated
Thanks Tom

 

[SkipVought]

HI,

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Whitoja]

Thanks Skip, The code is below, it's only the "sort" part that i'm talking about.

The "S_2" reference is the worksheet name but i will need to go to other worksheets and sort.So,somehow the worksheet reference has to generic, not a specific sheet like S_2.


Sub Sort()
'
' Sort Macro
'

'
Range("A14:CP33").Select
ActiveWorkbook.Worksheets("S_2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("S_2").Sort.SortFields.Add Key:=Range("A14:A33"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("S_2").Sort
.SetRange Range("A14:CP33")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sheet1").Select
Range("D14:CP32").Select
Selection.Copy
Sheets("S_2").Select
Range("D14").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B12").Select
End Sub

 

[SkipVought]

do NOT name your macro Sort as this is a reserver word.

This code assumes that 1) each table to sort in the workbook, is contiguous with A14 and 2) no data, other than the table to be sorted, is contiguous with this data.

Sub SortIt()
'
' Sort Macro
'

'
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        With ws.Sort
            With .SortFields
                .Clear
                .Add _
                    Key:=ws.Range("A14").Cells(1, 1), _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending, _
                    DataOption:=xlSortNormal
            End With
            .SetRange ws.Range("A14").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

sorry, had some extraneous code...

Sub SortIt()
'
' Sort Macro
'

'
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        With ws.Sort
            With .SortFields
                .Clear
                .Add _
                    Key:=ws.Range("A14"), _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending, _
                    DataOption:=xlSortNormal
            End With
            .SetRange ws.Range("A14").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[alchohaz]

I have a similar post and am new to all of the VB Code stuff, although i am getting to grips with it slowly, i am also going blind trying to understand it all !! So if the above already addresses my question then i apologise but could you add some notes to make it clearer for me?

My issue is as follows:

I have recorded a Macro that will start at Cell C2, from there i keyed CNT+SHFT+DOWN ARROW to select all of the rows available (the reason i have used this is that the amount of rows will change from sheet to sheet, but i would like to use the same Macro)..

Anyway when i run this macro it always applied the same values of that recorded in the code DESPITE using the CNT+SHFT+DOWN ARROW to select the required rows.

The problem in the code appears to be here:


ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E15") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers

Where ranges E2:E15 & C2:C15 are defined, i still want to sort in this column but do not want it stop at 15...

Full code below - NB i have tried running this by selecting the required data and then naming the range in Excel, but this still fails !!! Grrr PLEASE HELP


Sub Demo_RCD()
'
' Demo_RCD Macro
'

'
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E15") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C15") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:J15")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[SkipVought]

alchohaz,

Please post your own questions in a NEW thread.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Whitoja]

My situation is that i want to be able to sort whatever sheet i happen to be on at any given time, not "sheet1" all the time for example. the next time i want to sort it might be "sheet5" but i don't want to have to specify the sheet in the code, i need some generic reference to whatever sheet i happen to be on.
John

 

[PHV]

Like this ?

Sub SortIt()
With ActiveSheet.Sort
  With .SortFields
    .Clear
    .Add _
        Key:=Range("A14"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal
  End With
  .SetRange Range("A14").CurrentRegion
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886