Macro only working on first sheet - use of Rand=() for Sort and Filter - smallest to largest

[robbertly]

Hello,

I have an excel workbook with 24 sheets. I have created a macro (see below) which I hoped to use on all 24.

Unfortunately it only seems to work 'properly' on the first sheet (the sheet the macro was created on) and not 'properly' on the rest.

When I say properly I mean the macro is a 'shortcut' to Sort and Filter - Sort smallest to largest using Rand=()

So on the first sheet the Rand=() numbers change and the content in the colums to the right of the Rand=() change (random order), however on other sheets only the Rand=() number changes, but the content in the colums to the right of the Rand=() do not change.

Any suggestions welcome!!

Sub Macro1()
'
' Macro1 Macro
' random
'
' Keyboard Shortcut: Ctrl+q
'
    Range("C3").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C3:N13")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C3"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C3:N13")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D16").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D16"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("D16:N2063")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D16"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("D16:N2063")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Fluff]

Are you trying to get it to work on the active sheet?
Also why are you sorting both ranges twice?

 

[robbertly]

Hi,

Thanks for following up so quickly.

Yes, I am trying to get to work on all sheets in the workbook including the active sheet.

Effectively, I would like to be able to open the workbook, run the macro, have all worksheets affected and be able to save the workbook with the 'new' changes caused by the macro.

This would give me the option to use it as a template workbook to create multiple different work books with the same data output randomly.

As for sorting both ranges twice, I thought 'two' sorts might give a greater randomisation result than one sort, but its not essential.

I am willing to any changes to make it work.

Thanks,

 

[Fluff]

Ok how about

Sub robertly()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      With Ws.Sort
         .SortFields.Clear
         .SortFields.Add Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .SetRange Range("C3:N13")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
         
         .SortFields.Clear
         .SortFields.Add Key:=Range("D16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .SetRange Range("D16:N2063")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With
   Next Ws
End Sub

 

[robbertly]

Hello Fluff,

Yes, that worked absolutely perfectly... many thanks.

Your assistance is very much appreciated.... has saved me many hours of futile manual labour .

Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.