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

Hi

You miss the range statement:

..SetRange Range(RngName)

Regards,
Per

"Rich" <(E-Mail Removed)> skrev i meddelelsen
news:97AD54CB-DED8-4A76-9EB0-(E-Mail Removed)...
>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

Try this

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

Set SortRange = ActiveSheet.Range("A1").CurrentRegion

SortRange.Sort _
key1:=Range("E1"), _
Order1:=xlAscending, _
key2:=Range("F1"), _
Order2:=x_lAscending, _
key2:=Range("G1"), _
Order2:=xlAscending, _
Header:=xlYes
End Sub

"Rich" wrote:

> 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

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" <(E-Mail Removed)> skrev i meddelelsen
> news:97AD54CB-DED8-4A76-9EB0-(E-Mail Removed)...
> >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
>
>

It worked, Joel . . . but I had to make one modification . . .

It didn't like the variable (even with "Option Explicit" rem'd out!). So I
put replaced the var "SortRange" with the full statement. It worked find.

Thank you.

Rich

"joel" wrote:

> Try this
>
> 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
>
> Set SortRange = ActiveSheet.Range("A1").CurrentRegion
>
> SortRange.Sort _
> key1:=Range("E1"), _
> Order1:=xlAscending, _
> key2:=Range("F1"), _
> Order2:=x_lAscending, _
> key2:=Range("G1"), _
> Order2:=xlAscending, _
> Header:=xlYes
> End Sub
>
> "Rich" wrote:
>
> > 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

You need to change the last sort order and key from 2 to 3. I copied the
line and forgot to make the change. I don't like the SORT fields method in
VBA. The sort command the way I did it is much simplier.

"Rich" wrote:

> It worked, Joel . . . but I had to make one modification . . .
>
> It didn't like the variable (even with "Option Explicit" rem'd out!). So I
> put replaced the var "SortRange" with the full statement. It worked find.
>
> Thank you.
>
> Rich
>
> "joel" wrote:
>
> > Try this
> >
> > 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
> >
> > Set SortRange = ActiveSheet.Range("A1").CurrentRegion
> >
> > SortRange.Sort _
> > key1:=Range("E1"), _
> > Order1:=xlAscending, _
> > key2:=Range("F1"), _
> > Order2:=x_lAscending, _
> > key2:=Range("G1"), _
> > Order2:=xlAscending, _
> > Header:=xlYes
> > End Sub
> >
> > "Rich" wrote:
> >
> > > 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

With ActiveSheet.Sort
.SetRange "TestRange" ' you must use a range name not an
address
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Thank you . . . good eye for your own work. Will do!

"joel" wrote:

> You need to change the last sort order and key from 2 to 3. I copied the
> line and forgot to make the change. I don't like the SORT fields method in
> VBA. The sort command the way I did it is much simplier.
>
> "Rich" wrote:
>
> > It worked, Joel . . . but I had to make one modification . . .
> >
> > It didn't like the variable (even with "Option Explicit" rem'd out!). So I
> > put replaced the var "SortRange" with the full statement. It worked find.
> >
> > Thank you.
> >
> > Rich
> >
> > "joel" wrote:
> >
> > > Try this
> > >
> > > 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
> > >
> > > Set SortRange = ActiveSheet.Range("A1").CurrentRegion
> > >
> > > SortRange.Sort _
> > > key1:=Range("E1"), _
> > > Order1:=xlAscending, _
> > > key2:=Range("F1"), _
> > > Order2:=x_lAscending, _
> > > key2:=Range("G1"), _
> > > Order2:=xlAscending, _
> > > Header:=xlYes
> > > End Sub
> > >
> > > "Rich" wrote:
> > >
> > > > 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