Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Sorting multiple worksheets at once

    A friend creates an Excel workbook with a different worksheet for each month of the year--so 12 in all. He uses columns A through I, with a header [row 1]. He supplies this to me monthly for some non-profit work I do. The header names are the same on every worksheet; that is, Column A is always Date, Column B is always Park, Column C is Facility, etc.

    I have to do a two level custom sort [Park A-Z 1st, then Date, Oldest to Newest]. I can easily do this for each worksheet, but that requires 12 manual sorts--one for each month/worksheet. I want to reduce my work by sorting all 12 worksheets at once. But I cannot find a way to do this. As soon as I select more than one worksheet, the Sort & Filter function is grayed out (I use Office/Excel 2007).

    I have searched on line, but I cannot find anything relevant to this problem. On this forum, I found the thread 'Sort multiple worksheets (2002/2003)', but it does not seem to address my issue.

    Is there a way to sort all 12 worksheets simultaneously as I described?


    Thanks,

    Harry

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    hmw,
    below is the code to loop through your sheets and sort according to park then date. You can add as many columns ,rows, and sheets as you like. The code is generic to accommodate

    HTH,
    Maud

    Code:
    Public Sub SortAll()
    Dim sht As Worksheet
    Dim LastRow As Long, LastCol As Long
    For Each sht In ThisWorkbook.Worksheets
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
        sht.Sort.SortFields.Clear
        sht.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        sht.Sort.SortFields.Add Key:=Range("A2:A" & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With sht.Sort
            .SetRange Range(Cells(1, 1), Cells(LastRow, LastCol))
            .Header = xlYes
            .Apply
        End With
    Next sht
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-05-24 at 00:55.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Change ActiveSheet in Maud's code to sht to allow for different number of data rows on each sheet.

    I prefer the simplicity of pre-Excel2007 sorting methods (which still work) if the sorting requirements are simple. So you can also use this method:
    Code:
    Sub sortMonthSheets()
    
    For Each zSht In ThisWorkbook.Worksheets
    zSht.[a1].CurrentRegion.Sort Header:=xlYes, _
        key1:=zSht.[b2], order1:=xlAscending, _
        key2:=zSht.[a2], order2:=xlAscending
    Next zSht
    End Sub
    This works OK for sorting on ALL sheets in a workbook. If you have other sheets in the workbook that you don't want to sort, you need a method to restrict which sheets you want to include/exclude.
    One method might be to test for a specific known value in say, a heading row, that is common to the sheets you want. For example, you could check for say, heading in cell [C1] like this..
    Code:
    Sub sortMonthSheets()
    
    For Each zSht In ThisWorkbook.Worksheets
    With zSht
    If .[c1] = "Facility" Then
    .[a1].CurrentRegion.Sort Header:=xlYes, _
        key1:=.[b2], order1:=xlAscending, _
        key2:=.[a2], order2:=xlAscending
    End If
    End With
    Next zSht
    End Sub
    You could also check for sheet tab names e.g. like [2015-01], [2015-02], ..,[2015-12] using
    If zSht.Name Like "2015-*" Then
    do sort etc etc etc

    zeddy
    Last edited by zeddy; 2015-05-24 at 06:41.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Change ActiveSheet in Maud's code to sht to allow for different number of data rows on each sheet.
    Nice pick up Zeddy

    Should be:
    Code:
    LastRow = sht.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = sht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    Maud

  5. #5
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    hmw,
    below is the code to loop through your sheets and sort according to park then date. You can add as many columns ,rows, and sheets as you like. The code is generic to accommodate
    I copied it, and it works with the sample spreadsheet you provided. I think it will work fine for my needs.

    But I know almost nothing about Macro's, and the Excel Help on the subject was not much help to me. I saw that I should be able to install your macro in something called Personal Macros, so I can use it on any workbook. Unfortunately, I could not get this to work. So I also need some help here.

    Also, you have this macro set up so that there is a box called 'Sort' on the January worksheet. I like the idea of the box, but it does not appear on any other month. Does that mean I need to go to January each time I need to run the sort?

    Harry

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi Harry,

    Below is a revised workbook where I copied the button on Jan Sheet and pasted on each of the other months. You will be able to sort all the sheets from any one sheet. You could leave just the one button on the January sheet and sort from there if you want. Also please note that I applied the change from post #4 that Zeddy pointed out into this code so use this code to paste into your workbook.

    To paste the code, Press Alt-F11 to open the VB Editor window.
    Click Insert (top menu) > Module. A module window will appear on Right side. Paste the code here:

    hmw1.png

    There is a personal macro workbook that you can create and store your macros and have them available for access to your workbooks but there is a caveat to doing this. The Personal macro workbook should contain only generic macros that can be run on any workbook whereas this macro was designed specifically for this workbook. Also, if you copy the workbook and give it to a coworker, client, friend, et., the macro will not be included so an error will be generated when they click the button. So, best to follow instructions in paragraph 2.

    If you wish to learn more about the personal macro workbook, you can take look here:

    http://chandoo.org/wp/2013/11/18/usi...acro-workbook/

    HTH,
    Maud
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I'm obviously missing something. I can paste the code, as you said, after I have opened hmw_revised3.xlsm.

    Step #2 of the attached was how to create a Personal Macro Workbook, which I already had, so I went to Step #3. I was able to find Module1 under the Modules folder, which is a subset of VBAProject hmw_revised3.xlsm. I have the Properties window open, so gave it a name. [The same code also shows under VBAProject PERSONAL.XLSB, Module 1.]

    Then I Closed & Returned to Excel. Alt+F8 opens the macro list. As long as I have hmw_revised3.xlsm open, the macro shows and runs whether in All Open Workbooks, This workbook, or hmw_revised3.xlsm. But when I close that file, open my ParkCalendar.xls, and Alt+F8, the macro does not appear in any of the 'Macros in:' choicess. And when I open VBA and select the file, the code does not show.

    So obviously I have missed a critical step somewhere, and not saved it. But I can't determine how or where.

    Harry
    Last edited by hmw; 2015-05-24 at 17:57.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    hmw,

    Follow these steps:

    1. Close all instances of Excel the open a new instance.
    2. Record a new macro either by selecting the developer tab then Record Macro or clicking the record macro in the left lower corner of the Excel window

    hmw13.png

    3. In the Record Macro window, select for the "Store macro in:" field, Personal Macro Workbook. Accept the default macro name placed in the Macro Name field. click OK.

    hmw 17.png

    4. Now click Stop recording without recording any actions either on the Developer tab ribbon where the Record Macro was listed but now says Stop Macro or by clicking on the stop macro icon in the lower left of the Excel Window.

    5. Open the hmw_Revised3.xlsm workbook.

    6. Open the VB Editor by clicking Alt-F11

    7. Copy the code from the module in the hmw_Revised3 module and paste in the Personal.XLSB module.

    The Personal.XLSB module should look like the following:

    hmw18.png

    8. With the Personal.XLSB showing, in the VB Editor click File > Save Personal.XLSB
    hmw19.png

    9. Close all instances of Excel and open a new instance with a blank workbook then click Alt-F8

    hmw20.png

    The SortAll macro in your Personal.XLSB should be present along with the dummy recorded macro used to create the Personal.XLSB Module. You can erase the dummy macro.

    Hope that clears up your question.
    Maud

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    hmw (2015-05-25)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi hmw

    Maud has shown how you can add a macro to your Personal Macro Workbook.

    Another way is to load a particular workbook that will do that particular job.
    The attached .xlsm macro file is the Tool which will allow you to select a Park file.
    This Tool will check the headings of the chosen file.
    If any sheet in the chosen file has Facility in the headers cell [C1], it is assumed it is a Park file.
    The sheets in the Park file will then be sorted.
    If you choose the wrong file, a message will be displayed.

    A sample Park file is also attached for you to test the Tool.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-05-25 at 08:48.

  11. The Following User Says Thank You to zeddy For This Useful Post:

    hmw (2015-05-25)

  12. #10
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    zeddy & maudibe:

    First thanks for all your patience and code in trying to help me solve this problem. Sorry I am such a slow learner.

    1. Now when I open Excel, it defaults to PERSONAL.XLSB, rather than to 'Book 1' that I am used to. I cannot find any place to change this so it defaults as 'Book 1' which is what I want.
    2. zeddy: Your code performs a Save after completion. I don't want that. I usually do a Save As, and give it another name. I saw the line in your VBA code, but I did not know how to delete it---plus your code is 'Read Only'. Could you delete this for me.

    zeddy.:
    The code works great on your example, and on a simple spreadsheet that I created. But there is a problem when I try it on the spreadsheet that I really want to use this on. Your code gives me a message which says that everything has been sorted, but in fact nothing has been sorted. I have attached the file in question. It is supplied to me as an .xls file, which is how I sent it to you. I saved it also as an .xlsx file, but it still did not work. The text on the header line appears to be spelled exactly as yours is. Perhaps you could look at it for me?

    maudibe:
    You have supplied very clear instructions (and thanks for the screenshots). But I am having several problems.
    1. On screenshot 43368, I never see the top section--that starts with 'Sub Macro1()', and ends with the first 'End Sub' . Nor do I see the horizontal line below that 'End Sub'.
    2. On line 7, you say to copy the relevant code to the Personal.XLSB module. But the relevant code is already there.
    3. On screenshot 43371 the Personal.XLSB!Macro1 line is there, but the Personal.XLSB!SortAll is not.
    4. And running Macro1 does nothing on a simple spreadsheet--the one that worked for zeddy's code.

    I am sure I am missing something simple, but I cannot figure out what.

    Harry
    Attached Files Attached Files

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi hmw

    ..it was a lot easier to fix with your sample file.

    Try the updated attached file.

    zeddy
    Attached Files Attached Files

  14. #12
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    zeddy:

    Works great on every file I tried it on. Wonderful!

    1. So what did you do to get this to work on the file I supplied, since Ver. 1a worked on simple files?
    2. Why did you save it as an .xls file rather than a .xlsm file like version 1a?
    3. Isn't it Newcastle, rather than Newcazzle?


    Harry

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Harry,
    When it defaults to Personal.XLSB go to View > Hide then restart excel.

    Maud

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Harry

    Answers to your questions:
    1. In the file you supplied, there are many blank rows interspersed with data rows. My first method used [a1].CurrentRegion to 'grab' the data block to be sorted. This current region method doesn't work if you have blank rows (or blank columns) within your data. (You can see what the CurrentRegion would be if you select cell [A1] and then press [Ctrl]-A ). The version v2 method uses an 'end up' from the bottom of the date column [A] to find the last data row i.e. Cells(Rows.Count, "A").End(xlUp).Row (like Maud's method)

    (In xls files, Rows.Count is 65536, while in .xlsx files Rows.Count is 1048576, so Rows.Count can be used for all Excel versions etc. )

    2. No real reason - I was just checking to see if the colour's I used stayed between different Excel versions.

    3. In keeping with my mission to use more zzzzz'z whenever I get away with it.

    Now, having seen one of your sample files, I have attached version v3 (.xlsm format) which will now give you the option to also get rid of unused columns and rows on each of the month sheets of the chosen Park File, after the data records have been sorted. If the checkbox is 'ticked', this will remove formats from unused columns and rows, and reset the UsedRange on each sheet, and make the file size more efficent when saving
    (Pressing [Ctrl ][End] in Excel takes you to the bottom-right-most cell of the used range)

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-05-26 at 11:53.

  17. The Following User Says Thank You to zeddy For This Useful Post:

    hmw (2015-05-26)

  18. #15
    2 Star Lounger
    Join Date
    Jul 2013
    Posts
    171
    Thanks
    5
    Thanked 0 Times in 0 Posts
    zzzzzzzzzzzzzeddy:

    V3 works perfectly! Thanks for all your effort and assistance on this issue for a guy who does not understand VBA (although I did buy a book on it last year, which I have not yet read).

    And I really appreciate your modification to eliminate unused rows. The Parks Department, who created this spreadsheet, have all those rows so they can easily add rentals & reservations. And for their records, they do some color coding. Events in yellow are ones that have been cancelled, but they want to keep them listed, etc..

    Although the spreadsheet is for their internal use, they send it to me once or twice a month because I do the website for Ottawa & Jermain Parks (www.ottawapark.org) which includes a Calendar (see the Calendar tab). So all I need are events in those two parks, which is why I do the sort. Your elimination of the blank lines makes it easier and quicker for me to read, and as you said, creates a smaller Excel file when I save it.

    Thanks again for all your work,


    Harry (no zzz's)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •