Sorting Multiple Sheets With the Same Range

Question

Trying to sort multiple sheets, used an array and unfortunately getting "Cannot set array" error. Not sure what is being done wrong here. Here is my code:

Sub Macro1()

Dim ws() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws() = wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
'
' Macro1 Macro
'

'
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
    .SetRange Range("A1:B6")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

How many sheets are you trying to sort? How many sheets in total? You may be able to just use For Each ws in Worksheets — urdearboy
– urdearboy, Commented Jun 13, 2018 at 0:39
@urdearboy 3 sheets total. I have a master sheet that I do not want to sort — Jose Cortez
– Jose Cortez, Commented Jun 13, 2018 at 0:41

urdearboy · Accepted Answer · 2018-06-13 00:55:08Z

2

You are referring to an array where you should be referring to a sheet. You declared ws as Variant & this variable does not have properties such as .Sort

Notice that the array can be used as a shortcut to the sheet name, not as a shortcut to the .Sheets("") object.

Sub Macro1()

Dim ws() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook
ws() = Array("Sheet1", "Sheet2", "Sheet3")
Dim i as Integer

For i = 0 To 2
    wb.Sheets(ws(i)).Sort.SortFields.Clear
    wb.Sheets(ws(i)).Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wb.Sheets(ws(i)).Sort
    .SetRange Range("A1:B6")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Next i

End Sub

edited Jun 13, 2018 at 0:55

answered Jun 13, 2018 at 0:43

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

I find it is always a good idea to use For i = lbound(ws) To ubound(ws). That way, no problem if Option Base 1 is used and it's one less thing to remember to change if you add/remove from the array.

user4039065 Over a year ago

You tackled the variant array vs worksheets object collection quite concisely. I would only be regurgitating what you pointed out and corrected so I directed my efforts to other inadequacies.

user4039065 · Accepted Answer · 2018-06-13 00:44:32Z

1

The recorded Sort syntax is way more than you actually require.

sub macro2()
    dim w as long, lr as long, wss as variant

    wss = Array("Sheet1", "Sheet2", "Sheet3")

    for w = lbound(wss) to ubound(wss)
        with thisworkbook.worksheets(wss(w))
            lr = application.max(.cells(.rows.count, "a").end(xlup).row, _
                                 .cells(.rows.count, "b").end(xlup).row)
            with .range(.cells(1, "a"), .cells(lr, "b"))
                .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlNo
            end with
        end with
    next w
end sub

answered Jun 13, 2018 at 0:44

user4039065

Collectives™ on Stack Overflow

Sorting Multiple Sheets With the Same Range

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related