The goofy this is that I had tried it that way; when I did, I received an
error on the ".Apply" line, which gave me an "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."

I'm figuring that I must be defining and/or stating something incorrectly.

I'm trying a few more things, but the reply from "Joel" worked, with (oddly)
one slight modification.

I'm still new to VBA and don't do it that often to really understand nearly
as much as I need to!

Thanks again!

Rich

"Per Jessen" wrote:

Hi

You miss the range statement:

..SetRange Range(RngName)

Regards,
Per

"Rich" skrev i meddelelsen
...
A user sent me a spreadsheet with a recorded macro. He needed it changed to
work on different worksheets within the workbook.

I changed the macro so that it used "ActiveSheet." instead of a particular
worksheet.

I also changed it so that it finds the area to sort and then assigns a Var
name to the Range.

But, now it bombs on a particular line, which I have rem'd as, "THIS LINE
CRASHES THE MACRO" in the below code. Amongst the various error msgs I've
received while changing things around, the current code (below) generates
a
"Run-time error '13':/Type mismatch" error msg. What the heck am I doing
wrong!?

================================================== ===
Option Explicit
Sub SortCurrentSheet()
'
' Sort Macro
' Sorts Timesheet data per project number
'
' Keyboard Shortcut: Ctrl+s

Dim RngName As String

'Following code selects the data to be sorted:
Range("A1").Select
RngName = ActiveSheet.Range("A1").CurrentRegion.Address
ActiveWorkbook.Names.Add Name:="TestName", RefersToR1C1:=RngName

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 4).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 5).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 6).Range(RngName), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange RngName 'THIS LINE CRASHES THE MACRO
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
================================================== ===

Thank you for any assistnace.

Rich