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

"robs3131" wrote:

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

--
Robert