VBA Sort Help

**JayWeb** · 12-16-2010, 08:47 AM

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

Private 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

Thanks for any help

Jay

**Domski** · 12-16-2010, 08:55 AM

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

**JayWeb** · 12-16-2010, 10:17 AM

Doesnt work, same error.

Also I dont want to loop through all sheet, just the second sheet onwards.

**Domski** · 12-16-2010, 10:48 AM

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