Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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)... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Sort Columns on Workbook Open
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto open a workbook | Excel Discussion (Misc queries) | |||
Auto open workbook(s)? | Excel Discussion (Misc queries) | |||
Auto Sort 2 columns | Setting up and Configuration of Excel | |||
Is there a function to auto-sort 2 columns? | Excel Worksheet Functions | |||
auto open a workbook | Excel Programming |