I have the following code, I need it to sort all the sheets in my workbook on the first column. The below code doesnt seem to work for me though.
I get the error:
Run-time error '1004':
The sort reference is not valid.
When I make a macro the sort code line is exactly the same though :-S
Thanks for any helpPrivate Sub CommandButton1_Click() For x = 2 To Application.Sheets.Count Sheets(x).Activate Sheets(x).Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Next End Sub
Jay
Try this:
Private Sub CommandButton1_Click() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Range("A1").CurrentRegion.Sort Key1:=.Range("A2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next ws End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Doesnt work, same error.
Also I dont want to loop through all sheet, just the second sheet onwards.
Sorry missed that, try:
Private Sub CommandButton1_Click() Dim x As Long For x = 2 To ActiveWorkbook.Sheets.Count With Sheets(x) .Range("A1").CurrentRegion.Sort Key1:=.Range("A2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With Next x End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks