How can I sort my data in alphabetic order?

Sort macro to run on most but not all worksheets

• QUESTION: I am using2007 and would like to have a macro that automatically runs when I open a workbook to sort the information based on the values entered into column E. Most of the worksheets are sorting based on the values in column E but I have a few that are not. I have even noticed that if I add a new value in column E the sort is not working. The worksheet that I want the sort to start running is named "template" and all of the other worksheets that I want to run are placed after the template worksheet. Can you help me figure out what I am doing wrong please? I am not familiar with writing Macros and I had someone with AllExperts help to write the one listed below. Thank you. Amy. Sub auto_Open() ' Auto_Open Macro 'ActiveWorksheet.Save CutCopyMode = False With Worksheets("template") End With Dim book As Workbook Dim flagIndex As Integer Dim flagSheet As Worksheet Set book = ActiveWorkbook Set flagSheet = book.Worksheets("template") flagIndex = flagSheet.Index Dim sheetIndex As Integer Dim currentSheet As Worksheet For sheetIndex = flagIndex To book.Worksheets.Count Set currentSheet = book.Worksheets(sheetIndex) With currentSheet Rows("17:102").Select .Sort.SortFields.Clear .Sort.SortFields.Add Key:=Range( _ "E17:E102"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("template").Sort .SetRange Range("A17:ZZ102") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With End With Next End Sub ANSWER: Amy The code is set to sort rows 17 through 102. If you have data below that it won't be included in the sort. You need to decide how you want to identify the data to be sorted. You could give it a range name, and make the range dynamic (that is based on the cells that are filled in) or you could possibly use the "Current Region" if you data is organized appropriately. If you don't have any blank rows or blank columns and the data you want to sort is in the top left of the worksheet, AND you want to sort everything in the top left section of the worksheet then this could work for you Private Sub Workbook_Open() ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Cells(1, 1).CurrentRegion .SortFields.Add Key:=Range("e1:e14") .Header = xlYes .MatchCase = False .Orientation = xlTopToBot .SortMethod = xlPinYin .Apply End With End Sub This will sort any new rows you add, provided they are right below the existing rows. It will always sort based on column E. ---------- FOLLOW-UP ---------- QUESTION: Thanks Ken. I do want to only sort rows 17 through 102 and there are some blank rows and columns at this point but those may be filled in at a later time. How do I go about doing the range name and making it dynamic? Thanks again. Amy

• Answer:

  Amy Under formulas, define name; enter a range name like database or amy or something. Under Refers to (bottom of the form) enter =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),702) You may need to adjust this a little. It defines the range (say database) to be the cells from A1 out to column ZZ and from row 1 down the number of non blank cells in column A; probably 102 less the rows not yet filled in, plus any other rows that may have data in column A below the range of interest for sorting. The syntax of the OFFSET formula is =OFFSET(top left cell, rows down, columns to the right, rows to include, columns to include). It appears that you data starts in A1 so the next two arguments are 0. Then you have a dynamic number of rows, and 702 columns (to get out to colum ZZ).