Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sort multiple worksheets (2002/2003)

    I have volunteered myself into trouble.

    We had a worksheet for all employees with different types of information across the worksheet (almost to column IV). I thought that the workbook would be smaller in size and easier to find information if the one worksheet was divided into multiple worksheets with basic employee info (name, etc.). This made the workbook much smaller and now the managers can find the information they wanted.

    Now the issue. They want to sort all the worksheets into a different order. Of course, each worksheet has different information on the employees for multiple columns (monthly or weekly data on each worksheet. Each employee is listed on the same row on each worksheet. But each worksheet has data in a different number of columns.

    Is there any way to sort multiple worksheets at the same time? or create a macro to select all the data on the worksheet and sort on the same fields (column headings) on each worksheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort multiple worksheets (2002/2003)

    Please provide more info. For example:

    - Are the column headings on which you want to sort in the same location on all sheets? If so, which columns? Otherwise, what are the names of the fields on which you want to sort?
    - Which row contains the column headings?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    The column headings are on row 10 of each worksheet. The sort columns could be B, C, D, E for State, Office, Last name, First name. The original sort is D, E for Last name, First name.

    Thanks for any help that you can give.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort multiple worksheets (2002/2003)

    See if this does what you want. Test it on a copy of the workbook first!

    Sub SortEachSheet()
    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    With wsh.Range("B10").CurrentRegion
    .Sort Key1:=wsh.Range("E10"), Order1:=xlAscending, _
    Header:=xlYes
    .Sort Key1:=wsh.Range("B10"), Order1:=xlAscending, _
    Key2:=wsh.Range("C10"), Order2:=xlAscending, _
    Key3:=wsh.Range("D11"), Order3:=xlAscending, _
    Header:=xlYes
    End With
    Next wsh
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    Thanks, Hans, this is a start.

    I forgot to mention that there is one worksheet that is the summary and should not be sorted. Currently the summary worksheet is the first worksheet. Some of the worksheets have total rows below the data. I can move those rows down so they are not selected for the sorting.

    On the second worksheet the headings were included in the sort and the data moved to row 7. On the third and fourth worksheets again the headings were included, data was moved to row 2. The other worksheets also had issues with headings and moving data rows. There are some worksheets that have empty columns, but I can fix that.

    The data in columns A-E sorted as expected, but the other data some of the worksheets was not correct. If we get a macro to work, I can get the data without blanks so that all is selected.

    Do you have any other ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort multiple worksheets (2002/2003)

    You should ensure that all sheets that you want to be sorted have the column headers in the same row, and that the range to be sorted is contiguous. Otherwise it would become very complicated.

    The following version skips a sheet named "Summary":

    Sub SortEachSheet()
    Dim wsh As Worksheet
    For Each wsh In ActiveWorkbook.Worksheets
    If Not wsh.Name = "Summary" Then
    With wsh.Range("B10").CurrentRegion
    .Sort Key1:=wsh.Range("E10"), Order1:=xlAscending, _
    Header:=xlYes
    .Sort Key1:=wsh.Range("B10"), Order1:=xlAscending, _
    Key2:=wsh.Range("C10"), Order2:=xlAscending, _
    Key3:=wsh.Range("D11"), Order3:=xlAscending, _
    Header:=xlYes
    End With
    End If
    Next wsh
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    Hi, Hans.

    I have checked each worksheet to make sure that the headings are on row 10, but they all have information above row 10.

    I have selected starting in A10, End down arrow, End right arrow, to select all that I want to sort. Each worksheet selects and sorts all that I want to sort.

    When I run the macro each worksheet includes the headings in the sort and moves the data up several rows, even tho I did freeze frame on each worksheet. Because the other headings are overwritten with the data, there are circular reference on several worksheets.

    I am very bad at writing VBA code. I can understand what the macro is suppose to do, but do not know how to get it to work.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort multiple worksheets (2002/2003)

    The code completely ignores what cells you have selected.

    Make sure that the data that you want to sort (including the column headings) are in one contiguous area with at least one empty row above and below.

    If this means that the column headings have to be moved to another row, modify the code accordingly - for example if the column headings end up in row 12, change B10 to B12, C10 to C12 etc. (I should have used D10 instead of D11 in the macro, although it makes no difference for the result)

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Sort multiple worksheets (2002/2003)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Instead of sorting the worksheets you might consider this alternative.
    On the summary sheet put a list of the worksheets in the workbook.
    Next hyperlink the List so that when you select the link it goes right to the proper worksheet
    Next to the hyperlinks put in the various sort values.
    Then using sort or auto filter the user finds the worksheet or worksheets they desire and then uses the hyperlink to jump to the worksheet.
    This keeps the sheets in original order but lets users easily manipulate the workbook.
    See the attachement to <post#=643011>post 643011</post#> for VBA you can modify as needed. The VBA will create the Hyperlinks for the worksheets.

    Regards,

    Tom Duthie

  10. #10
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    Thanks, Hans

    With a blank row above the headings the headings stay where they belong and the data seems to sort. I will check to make sure all the data is sorting, but if we have no blanks in the data, it should be fine.

    I do have one question that came to my mind during the night (do't you just hate that), it we insert a row in the middle of the data, is there a way to get the new data (last name, first name, etc.) into all the worksheets as we enter them in the first worksheet? I thought there was a key combination to "enter" data on multiple worksheets.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    Thanks for the suggestion, Tom.

    I will study the attachment and see if I can get it to work for our application.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort multiple worksheets (2002/2003)

    You can select multiple sheets:
    - Click on a sheet tab, then Shift+click on another sheet tab to select a contiguous range of sheets.
    - Ctrl+click on individual sheet tabs to add them to or remove them from the selection.

    Actions such as inserting a row, entering data etc. will affect all selected sheets.
    Don't forget to select a single sheet again if you want to enter data in that sheet alone!

  13. #13
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Green Bay, Wisconsin, USA
    Posts
    396
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Sort multiple worksheets (2002/2003)

    Thanks, Hans.

    That was a Chicago Dah! on my part. I have been selecting all the worksheets, but forgot that while the worksheets are selected anything typed would be entered on all selected worksheets. Big Dah!

Posting Permissions

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