CODEWITHSUNDEEP
excelvbamacros
Navy
Navy

Reputation: 59

Excel Macro: Apply sorting to each sheet

Here is the code that I am using to apply sorting to each page except two. The system is throwing following error. "select method of range class failed".

Private Sub CommandButton3_Click() Dim ws1 As Worksheet

For Each ws1 In Worksheets
    If ws1.Name <> "Sheet1" And ws1.Name <> "Extra" Then

**ws1.Range("A1:V1000").Select**      Something is wrong here I suspect

ActiveWorkbook.Worksheets(ws1).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(ws1).Sort.SortFields.Add Key:=Range("I2:I1000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets(ws1).Sort.SortFields.Add Key:=Range("T2:T1000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

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

End If
Next ws1

End Sub

Please help.

Upvotes: 0

Views: 239

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

I would Select the worksheet before Selecting the range. Replace:

ws1.Range("A1:V1000").Select

with:

ws1.Select
Range("A1:V1000").Select

You may not need these Selections if you use .Range() rather than Range() in the code that follows. Also since ws1 is a worksheet object,:

ActiveWorkbook.Worksheets(ws1)

should be replace with:

ActiveWorkbook.Worksheets(ws1.Name)

There may be other problems with code.

Upvotes: 0

Related Questions