PDA

View Full Version : Solved: Sort All sheets in a workbook



satish gubbi
03-01-2013, 05:31 AM
Hi
I have below code, which I expect to sort all the sheets in a workbook, however its not working in all worksheets.

Please help me in rectifying this code.

Sub Sorty()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

With Range("E1").Select
Range("A:Z").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
On Error Resume Next


Next ws
End Sub

patel
03-01-2013, 09:50 AM
Sub Sorty()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Select
Range("A:Z").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
On Error Resume Next


Next ws
End Sub

satish gubbi
03-04-2013, 04:01 AM
Thank you very much Patel, code is working as intended, however I made little change

instead of

ws.select




ws.activate

patel
03-05-2013, 01:03 AM
why ?

satish gubbi
03-05-2013, 09:18 AM
Hi Patel,

with "ws.Select" code was sorting only one sheet, not all the sheets,

hence I tried "ws.activate" which worked as intended