Sorting Worksheets in Excel 1

[johngiggs]

I have an Excel workbook which contains hundreds of worksheets. The sheet names are the date in the format:

Month, Day YYYY i.e. October 22, 2003

Is there any way to sort the worksheet names (other than doing it manually) so that the worksheets are ascending? Any help would be greatly appreciated.

Thanks,

John

 

[SkipVought]

Hi,

Can only be done with VBA code

1. "extract" the sheet names to a blank sheet
2. sort the list
3. put the sheets in list order

Sub SortSheets()
    Worksheets.Add
    Set wsTmp = ActiveSheet
    wsTmp.Cells(1, 1).Value = "LIST"
    For Each ws In Worksheets
      wsTmp.Cells(wsTmp.Cells(1, 1).CurrentRegion.Rows.Count + 1, 1).Value = ws.Name
    Next
    wsTmp.Cells(1, 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    For Each n In Range(wsTmp.Cells(2, 1), Cells(2, 1).End(xlDown))
        Sheets(n.Value).Move After:=Sheets(Sheets.Count)
    Next
End Sub

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ireeves]

Excel doesn't have a built in tool do this, but there are some VBA codes out there you can try. Here are a couple of web links:

http://www.exceltip.com/show_tip/Fi...orkbook_using_VBA_in_Microsoft_Excel/475.html

http://www.cpearson.com/excel/sortws.htm

 

[johngiggs]

Skip,

Thanks!! I'll give that a try. Rather than going through the worksheets one by one, is there any chance that I can assume that the worksheets exist for every day of the week, even though in actuality they don't? I.E. if the workbook contains worksheets for January 1, 2003 through October 22, 2003, can I just use all of the days in that range even though worksheets were not created for some of those days or does the code require the input to be accurate?

Thanks,

John

 

[SkipVought]

John,

Don't believe that you can make the assumption that there is a sheeet for EVERY day of the week. Why do you want to

"...use all of the days in that range even though worksheets were not created for some of those days..."?

BTW, it is a really, REALLY BAD DESIGN to put similar data on separate worksheets -- REALLY BAD!

Your data ought to be in a SINGLE LIST at worst, that INCLUDES the DATE. There is no simple way to analyze your data (January 1, 2003 through October 22, 2003) like...

What are the results per month or results per quarter or results for the year to date???

Questions like that could be answered in A FEW SECONDS if ALL the data is in a list.

Think about it!

I D
   e
    s
     c
      e
       d, breathing........breathing aaaaahhhhhhh!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[johngiggs]

Skip,

The workbook only contains information about offsite tape storage and was created long before I began maintaining the document a few weeks ago. They likely used this convention for simplicity as the worksheets being created are the same format as the previous one, but the data is different as the tape numbers of the tapes being shipped offsite change daily. The worksheets are used as reports to print out and provide to the offsite storage company, so there is no real "data" that requires calculations quarterly or annualy. The worksheets are labeled as the current date for ease of reference when checking to see which tapes were shipped offsite on a specific date. This is not the most logical design, but I'm not going to spend countless hours moving all of the data into one worksheet for no reason. For it's simplistic purpose, the workbook is fine as is.

Thanks,

John

 

[SkipVought]

Well simply for the reason that this thread exists is reason enough. Seeming SIMPLICITY is not necessarily simplistic.

I can understand your not wanting to take the time to do what seems to be an academic excersize, but in my humble experience,

1. the knowledge and experience that you would gain would be worth while and

2. the EXTRA time that you, or someone else in your organization, will spend maintaining this workbook, will at some point give you a RETURN ON INVESTMENT.

If the sheet to sheet format is IDENTICAL, the COMPOSITE sheet could be built and assembled in a few mintes via VBA code.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com