Auto Sort Columns on Workbook Open

Hey everyone... Since this forum has been so helpful, I thought I would
try another one!

I have a worksheet with 10 columns, and an ever number of growing rows.
What I would like to do is to Sort Column 'B', along with all the
other respective data in the other columns, each time the spreadsheet
opens. I would prefer to use VBA or some other auto-launching event.

As always, thanks for any help that you guys can provide!!

There are a lot of sort options--I may have not chosen the ones you want.

But this may give you a start:

Option Explicit
Sub auto_open()

Dim myRng As Range

With Worksheets("Sheet1")
Set myRng = .Range("a1:j" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With myRng
.Cells.Sort key1:=.Columns(2), order1:=xlAscending, _
header:=xlYes
End With

End Sub

I used column A to find the last row of the range to sort.

stacy wrote:
>
> Hey everyone... Since this forum has been so helpful, I thought I would
> try another one!
>
> I have a worksheet with 10 columns, and an ever number of growing rows.
> What I would like to do is to Sort Column 'B', along with all the
> other respective data in the other columns, each time the spreadsheet
> opens. I would prefer to use VBA or some other auto-launching event.
>
> As always, thanks for any help that you guys can provide!!

--

Dave Peterson

I am sure you are already getting tired of seeing my name in this
forum, huh??

I must be doing something incorrectly... I have the following as a
smaller example of what I will be using. I copied the code, but it
didn't sort 'C' before 'D' when opened.

School School id Name
A 1 Angie
B 2 Barb
D 4 Maral
C 3 Brad

The theory is that user Brad has just entered a new school into the
sheet, saved it, and closed it. I would like "on open", the sheet to
notice that it is out of order, and place the 'C', and all subsequent
information to the right, after the 'B'... Make sense? Typically users
will go to the end of the sheet, and enter their infomation on a daily
basis, but when I open it to view, I would like it to all be sorted by
the school name... in this case A, B, C, D, etc..

Thanks again for any insight... I am sure I am missing something very
simple. (still very new to this)...

It worked ok for me with your data.

Did you change the code to point at the correct worksheet? (I used sheet1.)

Do you have macros enabled?

Did you paste the auto_open sub in a general module of that workbook's project?

If you click somewhere (anywhere) in that subroutine and hit F8 (to step through
the code), what happens?

What is in those cells? Is it just typed in or is it a formula that directly
points at another cell on a different worksheet, like:
=sheet2!b99

(These kinds of formulas don't work with sorts.)

stacy wrote:
>
> I am sure you are already getting tired of seeing my name in this
> forum, huh??
>
> I must be doing something incorrectly... I have the following as a
> smaller example of what I will be using. I copied the code, but it
> didn't sort 'C' before 'D' when opened.
>
> School School id Name
> A 1 Angie
> B 2 Barb
> D 4 Maral
> C 3 Brad
>
> The theory is that user Brad has just entered a new school into the
> sheet, saved it, and closed it. I would like "on open", the sheet to
> notice that it is out of order, and place the 'C', and all subsequent
> information to the right, after the 'B'... Make sense? Typically users
> will go to the end of the sheet, and enter their infomation on a daily
> basis, but when I open it to view, I would like it to all be sorted by
> the school name... in this case A, B, C, D, etc..
>
> Thanks again for any insight... I am sure I am missing something very
> simple. (still very new to this)...

--

Dave Peterson

Okay, i think I located the problem... I had not placed it in a Module,
but instead the Worksheet in the VBA window. I think it is working
perfectly now... Thanks once again Dave!!! Huge help!!!

Oh wait... One more quick question: When I sort the spreadsheet
manually, I get the window asking me if I want to sort everything that
looks like a number, as a number, which I do want... Some users have
placed things in here that are not picked up and sorted as a number.
Is there a way to build this into the code? Thanks again...

Are all your users running xl2002 or above?

If no, then this will break for users of older versions. (It was added in
xl2002.)

The sort statement has an optional parm that you can add for each key.

DataOption1:=xlSortNormal
or
DataOption1:=xlSortTextAsNumbers

Just add that to the end of the sort statement.

With myRng
.Cells.Sort key1:=.Columns(2), order1:=xlAscending, _
header:=xlYes, DataOption1:=??????????
End With

But the default value is xlSortNormal. So that range should treat text numbers
and number numbers separately.

I'm not sure what you're asking???


stacy wrote:
>
> Oh wait... One more quick question: When I sort the spreadsheet
> manually, I get the window asking me if I want to sort everything that
> looks like a number, as a number, which I do want... Some users have
> placed things in here that are not picked up and sorted as a number.
> Is there a way to build this into the code? Thanks again...

--

Dave Peterson

Nope... You hit it right on the mark!! I have people in this thing
that shouldn't be, and they have entered numbers as text, numbers as
numbers, and numbers as God knows what!! So when I do a simple "select
column 'B', and sort", it prompts me that some numbers are text, etc,
and how do I want to treat them. I then tell it to treat anything at
all that resembles a number, as a number, and sort it... Works fine.
Your solution aboves is just what I needed!!! Works great!! Needless
to say I am going to have few words with a select group of users making
my world harder than it should be!! )

Thanks again Dave!!! Perfect solution!

If it weren't for users, life would be simpler <vbg>.

stacy wrote:
>
> Nope... You hit it right on the mark!! I have people in this thing
> that shouldn't be, and they have entered numbers as text, numbers as
> numbers, and numbers as God knows what!! So when I do a simple "select
> column 'B', and sort", it prompts me that some numbers are text, etc,
> and how do I want to treat them. I then tell it to treat anything at
> all that resembles a number, as a number, and sort it... Works fine.
> Your solution aboves is just what I needed!!! Works great!! Needless
> to say I am going to have few words with a select group of users making
> my world harder than it should be!! )
>
> Thanks again Dave!!! Perfect solution!

--

Dave Peterson

Hi there, I was wondering if you can help.

Apologies for opening up an old thread but this thread is how I found the site and came up on my google search.

I offer my further apologies as I am not great at using excel but thanks to people like yourselves and sites like this, I am getting better.



Is there any way to apply this formula to all columns?

I have a spreadsheet that will continually be added onto. I would like it to sort automatically upon opening.

This is good as it sorts one column, is there an option though to sort all columns independently, so that column a is sorted, then column b is sorted, then c is sorted etc.

Thanks for any help I may receive.