Need help with error in macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Someone was great enough to help me out yesterday with some code, but it's
not working 100%. Could someone please help me out? I have a multi-user,
multi-sheet spreadsheet that I am trying to have a macro sort the data in
each sheet when the user goes to save the file. The code I have is stated
below.

1) It currently sorts all data, including the header row, which I don't want
to sort in.
2) it errors out on the first line containing the ".Sort key1...". the error
is "Run-time error 1004: Application-defined or object-defined error"

Any ideas or solutions will make my life much better!!!! Thank you sooo much
and may you have a blessed Christmas Day!

Here's the code!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'
Dim WS As Worksheet
Set WS = Worksheets("Kathryn")
With WS.UsedRange

..Sort key1:=.Range("Project"), order1:=xlAscending
..Sort key2:=.Range("Assignment"), order2:=xlAscending
End With

Set WS = Worksheets("Dave")
With WS.UsedRange

..Sort key1:=.Range("Project"), order1:=xlAscending
..Sort key2:=.Range("Assignment"), order2:=xlAscending
End With

Set WS = Worksheets("Ann")
With WS.UsedRange

..Sort key1:=.Range("Project"), order1:=xlAscending
..Sort key2:=.Range("Assignment"), order2:=xlAscending
End With

End Sub
 
It is not clear to me how this is making Excel crash. Barring that,
Programming would have been a more appropriate forum, that would have
produced an almost immediate response.

From the VBA editor, Help for "Sort Method" provides most of your
answers. You are currently trying to sort each sheet twice. The second
sort does not specify the first sort key. Best guess is that you meant
to sort once with two keys. Also, header is discussed there. Try

.Sort key1:=.Range("Project"), order1:=xlAscending, _
key2:=.Range("Assignment"), order2:=xlAscending, header:=xlYes

The specific error that you specified is caused by the fact that you are
trying to sort one worksheet by keys that are on a different worksheet.
A named range in a workbook is tied to a specific sheet. You cannot
reuse the same names on each sheet in a workbook. Either used named
ranges that belong on each sheet, or specify the cell references instead
of range names.

Jerry
 
Back
Top