Issue with Sort

G

Guest

I used the record macro feature to get the basis for sorting a range and then
edited the code to take out the "select" actions [ie - select Sheets("...].
I then executed the macro and it worked. I then copied this edited macro
into a sub where I need this to execute and it failed with the message below.


What is very strange is that I then went back and executed the macro in the
sub where I originally recorded it -- sometime it worked with no issue while
other times the same error below came up -- and this was with no data being
edited in the sheet where the sort was ocurring. Any idea on what the issue
is? FYI - there are many rows of data in the range (there are no blanks in
the key column (column D)).


Error Message:
Run-time error '1004: The sort reference is not valid. Make sure that it's
within the data you want to sort, and the first Sort By box isn't the same or
blank.

Code:
With Sheets("Member ID Report Master")
.Cells.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
J

Jim Cone

Robert,
It can be deceptive. Just one dot is all you need...
..Range("D2")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"robs3131" <[email protected]>
wrote in message
I used the record macro feature to get the basis for sorting a range and then
edited the code to take out the "select" actions [ie - select Sheets("...].
I then executed the macro and it worked. I then copied this edited macro
into a sub where I need this to execute and it failed with the message below.


What is very strange is that I then went back and executed the macro in the
sub where I originally recorded it -- sometime it worked with no issue while
other times the same error below came up -- and this was with no data being
edited in the sheet where the sort was ocurring. Any idea on what the issue
is? FYI - there are many rows of data in the range (there are no blanks in
the key column (column D)).
Error Message:
Run-time error '1004: The sort reference is not valid. Make sure that it's
within the data you want to sort, and the first Sort By box isn't the same or
blank.
Code:
With Sheets("Member ID Report Master")
.Cells.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

You are missing a dot before Range("D2") Since you do not explcitly
reference it back to the with statement by using the dot it looks ar range
D2 on the active sheet. Give this a try...

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
 
G

Guest

The reason can be the fact that the Worksheet with name "Member ID Report
Master" is not active.

Try this
With Sheets("Member ID Report Master")
..Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

You use a relative sort key Range("D2") which means
ActiveWorkbook.Range("D2"). When worksheet "Member ID Report Master†is not
active, confusion takes place: the sort key belongs to another sheet, not to
"Member ID Report Masterâ€.

Try this

With Sheets("Member ID Report Master")
..Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

You use a relative sort key Range("D2") which implicitly means
ActiveWorkbook.Range("D2"). When worksheet "Member ID Report Master†is not
active, confusion takes place: the sort key belongs to another sheet, not to
"Member ID Report Masterâ€.
Try this:
With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

You use a relative sort key Range("D2") which implicitly means
ActiveWorkbook.Range("D2"). When worksheet "Member ID Report Master†is not
active, confusion takes place: the sort key belongs to another sheet, not to
"Member ID Report Masterâ€.

Correct code:
With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

You use a relative sort key Range("D2") which implicitly means
ActiveWorkbook.Range("D2"). When worksheet "Member ID Report Master†is not
active, confusion takes place: the sort key belongs to another sheet, not to
"Member ID Report Masterâ€.

Correct code:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

Put a period in front of Range("D2") so it is qualified by the With statement

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
 
G

Guest

You need to put a period in front of Range("D2") so it refers to the same
sheet as you are sorting - not the activesheet.

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
 
G

Guest

Actually I just realized what the issue was -- I needed to put a "." before
"Range". That solved the problem. Thanks (I'm on the right path now -
solving my own issues before someone can respond :)).
 
G

Guest

Because you are using With Sheets you need a . in front of range as follows
otherwise range refers to the active sheet and I assume that your error is
when Member ID Report Master is not the active sheet but it runs OK when it
is the active sheet.

With Sheets("Member ID Report Master")
.Cells.Sort _
Key1:=.Range("D2"), _
Order1:=xlAscending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

Regards,

OssieMac
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.
Correct code would be:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.

Correct code would be:
With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.
Correct code would be:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.
Correct code would be:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.
Correct code would be:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

The reason is that you tried to sort not active worksheet. In your code
Range("D2") implicitly means ActiveSheet.Range("D2"). There is confusion when
you try to sort sheet "Member ID Report Master" with a key belonging to
another sheet.
Correct code would be:

With Sheets("Member ID Report Master")
.Cells.Sort Key1:=.Range("D2"), Order1:=xlAscending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With
 
G

Guest

Try this code to see if it solves your issue. I don't see a range selected to
sort...change the "G10" in this code to suit your data range.

With Sheets("Member ID Report Master")
Range("D2:G10").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Runtime error 1004 Application-defined or object defined error" on data sort 0
Sorting Macro Loop 2
Sort in hidden page crach 8
Sort error? 2
SpecialCells(xlCellTypeBlanks) = error 1004 No cells found 5
Applying Variables to SORT 4
Modify Sort Routine to inlcude All Data 2
sort macro not working 1

Top