Sort all worksheets in a workbook
First, just a couple of points...
You'll want to use xltoleft, not xlleft. There's a difference:
?xltoleft
-4159
?xlleft
-4131
(from the VBE's immediate window)
I would use
with wks
instead of
with worksheets(wks.name)
And instead of using A65536 and IV4, I'd let excel determine the last row and
last column in the sheet:
Option Explicit
Sub EditSheets2()
Dim Wks As Worksheet
Dim LstRow As Long
Dim LstCol As Long
For Each Wks In ActiveWorkbook.Worksheets
With Wks
LstCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
LstRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(5, 1), .Cells(LstRow, LstCol)).Sort _
key1:=.Range("a5"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
Next Wks
End Sub
jlclyde wrote:
<<snipped
- Show quoted text -
I was looking for a way to sort all worksheets by two seperate keys.
I figured that part out and now need to resort the sheets by a
different key. I woudl like to Sort all sheets in the work book by A5
ascending. How is this done?
Thanks,
Jay
--
Dave Peterson
|