Need help. Sorting on save in multi-sheet workbook.

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

Guest

Yesterday, someone was wonderful enough to help me thus far, however, I'm
still having some problems. Below is the code I have that takes the
individual sheets and sorts them when the file is saved. I am getting an
error on the sort keys that 1) sorts the entire column, including the header,
which I don't want sorted into the data, and 2) stops after sorting the first
key and doesn't continue.

Also need to know if the way it is set up will continue on to each worksheet
in the workbook.... I hope so.

I appreciate any help... I'm hoping to have this up and running this week to
please my co-workers and boss.
----


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
 
Yesterday, someone was wonderful enough to help me thus far, however, I'm
still having some problems. Below is the code I have that takes the
individual sheets and sorts them when the file is saved. I am getting an
error on the sort keys that 1) sorts the entire column, including the header,
which I don't want sorted into the data, and 2) stops after sorting the first
key and doesn't continue.

Also need to know if the way it is set up will continue on to each worksheet
in the workbook.... I hope so.

I appreciate any help... I'm hoping to have this up and running this week to
please my co-workers and boss.
----

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
Click to expand...

You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct Excel newsgroup. I would suggest you
include your Windows and Office/Excel version number in the message.
 
Back
Top