VBA sort not working

**beechum1** · 05-26-2009, 09:39 PM

Error message is sort method of range class failed. 1004. Failure is in the red text.

Private Sub CommandButton2_Click()
'ActiveWorkbook.Save

Dim wbname As String

wbname = ActiveWorkbook.Name

Set NewBook = Workbooks.Add

With NewBook
.SaveAs Filename:="\\TONYDESKTOP\SharedDocs\PromanProject\LamReports\LamReport.xls"
End With

'Copying From ProMan to LamReport

Workbooks(wbname).Activate
Sheets("LamPro").Range("K9:AE38").Copy
Workbooks("LamReport.xls").Activate
Sheets("Sheet1").Select
ActiveSheet.Paste

'Moving Information

Sheets("Sheet1").Range("H1:N30").Cut
Sheets("Sheet1").Range("A31").Select
ActiveSheet.Paste

Sheets("Sheet1").Range("O1:U30").Cut
Sheets("Sheet1").Range("A61").Select
ActiveSheet.Paste

'Delete Column 1

Sheets("Sheet1").Columns(1).Delete

'Sort By Invoice Number

    Sheets("Sheet1").Range("A1:F250").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Header:=xlNone, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    
End Sub

**beechum1** · 05-26-2009, 09:54 PM

Recorded Macros and copy/pasted, still no worky.

    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

**JBeaucaire** · 05-26-2009, 09:55 PM

The code seems to be fine, though split oddly. Replace that line and the one above it with this:

    Sheets("Sheet1").Range("A1:F250").Sort Key1:=Range("A1"), Order1:=xlAscending, _
        Key2:=Range("C1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal

**beechum1** · 05-26-2009, 10:11 PM

Thanks JB,

"The sort reference is not valued. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

is the error message i recieve with that code.

and iv'e been working on it for an hour with different snippits from all around....