Macro to Sort automatically when file/save is selected

I have a shared file that is being used by some rather inexperienced Excel
users. This file will expand with extended use as each user enters their
data. In order to make it easier for them to use, I'm trying to automate some
things.

Is there a way... and I'm sure there is, I'm just not experienced enough to
know how... that I can have my file automatically sort on a particular column
when the user goes to save it? I'm thinking that this must be either a macro
or VBA or whatever, but I am rather a novice in this area.

Open the ThisWorkbook module, and use the BeforeSave event
procedure.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'
' your sort code here
'
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"KDG" <[email protected]> wrote in message
news:[email protected]...
>I have a shared file that is being used by some rather
>inexperienced Excel
> users. This file will expand with extended use as each user
> enters their
> data. In order to make it easier for them to use, I'm trying to
> automate some
> things.
>
> Is there a way... and I'm sure there is, I'm just not
> experienced enough to
> know how... that I can have my file automatically sort on a
> particular column
> when the user goes to save it? I'm thinking that this must be
> either a macro
> or VBA or whatever, but I am rather a novice in this area.

OK... first off, Thank you so much for your speedy response!
Second... I copied in this partial code into the VB editor on
"ThisWorkbook"... now (and I'm sorry if you feel you have to spoon feed me...
I get scared of code) Say my worksheet has a column with a header of
"Project" in column D and column E has "Assignment"... I would like it to
sort primary on column D and secondary on column E. How in the world to I do
this? I'm a good student, just one that has avoided learning any type of
programing or code for years and now it's coming back to bite me.

Thanks so much for your expertise and patience!!!

"Chip Pearson" wrote:

> Open the ThisWorkbook module, and use the BeforeSave event
> procedure.
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> '
> ' your sort code here
> '
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "KDG" <[email protected]> wrote in message
> news:[email protected]...
> >I have a shared file that is being used by some rather
> >inexperienced Excel
> > users. This file will expand with extended use as each user
> > enters their
> > data. In order to make it easier for them to use, I'm trying to
> > automate some
> > things.
> >
> > Is there a way... and I'm sure there is, I'm just not
> > experienced enough to
> > know how... that I can have my file automatically sort on a
> > particular column
> > when the user goes to save it? I'm thinking that this must be
> > either a macro
> > or VBA or whatever, but I am rather a novice in this area.
>
>
>

In the Workbook_BeforeSave procedure, use

Dim WS As Worksheet
Set WS = Worksheets("Sheet1") '<< change if required
With WS.UsedRange
.Sort key1:=.Range("D1"), order1:=xlAscending,
key2:=.Range("E1"), _
order2:=xlAscending
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"KDG" <[email protected]> wrote in message
news:[email protected]...
> OK... first off, Thank you so much for your speedy response!
> Second... I copied in this partial code into the VB editor on
> "ThisWorkbook"... now (and I'm sorry if you feel you have to
> spoon feed me...
> I get scared of code) Say my worksheet has a column with a
> header of
> "Project" in column D and column E has "Assignment"... I would
> like it to
> sort primary on column D and secondary on column E. How in the
> world to I do
> this? I'm a good student, just one that has avoided learning
> any type of
> programing or code for years and now it's coming back to bite
> me.
>
> Thanks so much for your expertise and patience!!!
>
> "Chip Pearson" wrote:
>
>> Open the ThisWorkbook module, and use the BeforeSave event
>> procedure.
>>
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
>> Cancel As Boolean)
>> '
>> ' your sort code here
>> '
>> End Sub
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "KDG" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a shared file that is being used by some rather
>> >inexperienced Excel
>> > users. This file will expand with extended use as each user
>> > enters their
>> > data. In order to make it easier for them to use, I'm trying
>> > to
>> > automate some
>> > things.
>> >
>> > Is there a way... and I'm sure there is, I'm just not
>> > experienced enough to
>> > know how... that I can have my file automatically sort on a
>> > particular column
>> > when the user goes to save it? I'm thinking that this must
>> > be
>> > either a macro
>> > or VBA or whatever, but I am rather a novice in this area.
>>
>>
>>

I'll eventually get this... OK... it's sorting. That's GREAT!!! However... do
I lock the top row in place before I start this? Right now my column headers
are sorting in with all the other data. Am I on the right track?
Also, this is a multi sheet document (one sheet for each user). In the code,
I'm assuming I copy that part of the code down and put the appropriate
"sheet" name in the appropriate spot (I'll try it and see if it works).
Again! Thanks sooooo much! You're really saving my life and making my boss
very happy!

"Chip Pearson" wrote:

> In the Workbook_BeforeSave procedure, use
>
> Dim WS As Worksheet
> Set WS = Worksheets("Sheet1") '<< change if required
> With WS.UsedRange
> .Sort key1:=.Range("D1"), order1:=xlAscending,
> key2:=.Range("E1"), _
> order2:=xlAscending
> End With
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "KDG" <[email protected]> wrote in message
> news:[email protected]...
> > OK... first off, Thank you so much for your speedy response!
> > Second... I copied in this partial code into the VB editor on
> > "ThisWorkbook"... now (and I'm sorry if you feel you have to
> > spoon feed me...
> > I get scared of code) Say my worksheet has a column with a
> > header of
> > "Project" in column D and column E has "Assignment"... I would
> > like it to
> > sort primary on column D and secondary on column E. How in the
> > world to I do
> > this? I'm a good student, just one that has avoided learning
> > any type of
> > programing or code for years and now it's coming back to bite
> > me.
> >
> > Thanks so much for your expertise and patience!!!
> >
> > "Chip Pearson" wrote:
> >
> >> Open the ThisWorkbook module, and use the BeforeSave event
> >> procedure.
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> >> Cancel As Boolean)
> >> '
> >> ' your sort code here
> >> '
> >> End Sub
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "KDG" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a shared file that is being used by some rather
> >> >inexperienced Excel
> >> > users. This file will expand with extended use as each user
> >> > enters their
> >> > data. In order to make it easier for them to use, I'm trying
> >> > to
> >> > automate some
> >> > things.
> >> >
> >> > Is there a way... and I'm sure there is, I'm just not
> >> > experienced enough to
> >> > know how... that I can have my file automatically sort on a
> >> > particular column
> >> > when the user goes to save it? I'm thinking that this must
> >> > be
> >> > either a macro
> >> > or VBA or whatever, but I am rather a novice in this area.
> >>
> >>
> >>
>
>
>

Another thing... it seems to be sorting on the first key, but I'm getting an
error on the second. "Run time error: Sort method of range class failed" is
what it says.

Any ideas???

"Chip Pearson" wrote:

> In the Workbook_BeforeSave procedure, use
>
> Dim WS As Worksheet
> Set WS = Worksheets("Sheet1") '<< change if required
> With WS.UsedRange
> .Sort key1:=.Range("D1"), order1:=xlAscending,
> key2:=.Range("E1"), _
> order2:=xlAscending
> End With
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "KDG" <[email protected]> wrote in message
> news:[email protected]...
> > OK... first off, Thank you so much for your speedy response!
> > Second... I copied in this partial code into the VB editor on
> > "ThisWorkbook"... now (and I'm sorry if you feel you have to
> > spoon feed me...
> > I get scared of code) Say my worksheet has a column with a
> > header of
> > "Project" in column D and column E has "Assignment"... I would
> > like it to
> > sort primary on column D and secondary on column E. How in the
> > world to I do
> > this? I'm a good student, just one that has avoided learning
> > any type of
> > programing or code for years and now it's coming back to bite
> > me.
> >
> > Thanks so much for your expertise and patience!!!
> >
> > "Chip Pearson" wrote:
> >
> >> Open the ThisWorkbook module, and use the BeforeSave event
> >> procedure.
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> >> Cancel As Boolean)
> >> '
> >> ' your sort code here
> >> '
> >> End Sub
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> "KDG" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a shared file that is being used by some rather
> >> >inexperienced Excel
> >> > users. This file will expand with extended use as each user
> >> > enters their
> >> > data. In order to make it easier for them to use, I'm trying
> >> > to
> >> > automate some
> >> > things.
> >> >
> >> > Is there a way... and I'm sure there is, I'm just not
> >> > experienced enough to
> >> > know how... that I can have my file automatically sort on a
> >> > particular column
> >> > when the user goes to save it? I'm thinking that this must
> >> > be
> >> > either a macro
> >> > or VBA or whatever, but I am rather a novice in this area.
> >>
> >>
> >>
>
>
>