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

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"
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

--
Robert

Range("D2") will refer to the active sheet. Try:

..Range("D2")


"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

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
--
HTH...

Jim Thomlinson


"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

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

"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

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

"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

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



"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

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

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

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

--
Regards,
Tom Ogilvy

"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

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

--
Regards,
Tom Ogilvy


"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

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 :)).
--
Robert


"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

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


"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

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


"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

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


"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

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


"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

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

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


"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

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


"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

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

"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

You need to use ".Range" to ensure you utilise the with statement...

"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

I think you need to use ".range" so that the "with" is used....

"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

Robert,
Thanks very much for the response. I think I mentioned that I'm relatively
new to excel, so with that being said I have to admit that I don't have the
skillset to follow your instruction of recording a macro. I can begin
recording a macro, but once I open the function it's all forgeign to me. I
have no idea what to do from there. Is there a step-by-step instruction
anywhere that you are aware of that can walk me through the basics of this
macro? I don't even know where to enter code, other than each specific cell
or range..but even then I don't have much of a comfort zone.

--
Thanks

trojanTRADE


"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

hi,
I think you crated the error when you removed the select actions. in your
edited code, you do specify the sheet to sort but not the range to sort.
Unless the curser is in the range to sort, excel does not know what to sort.
try an experiment. click a nonadjacent blank cell to the right of your data
and try to sort you data. oops

Regards
FSt1

"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