Macro for sorting data on multiple sheets

Microsoft Excel 2007
October 25, 2009 at 12:09:34
Specs: Windows XP
I have created a budget program with 26 sheets for bi-weekly payroll. The first 4 columns contain employee info starting with last name. Hourly data is entered starting in column E. The employee info is copied to all of the additional sheets with formulas that point to the first sheet. If a new employee is added, his or her name is added to the bottom of the list on the first sheet. I created a name sort macro to resort the employee info back into a-z order. The problem is that on the subsequent sheets, the names and info are in the correct order but their data didn't resort with their names. The only correct sheet is the first one. As more sorts have been done, more errors have occurred. Is there any way to create a macro that would sort all the names and their hourly data on all of the sheets at the same time?

See More: Macro for sorting data on multiple sheets

Report •

October 25, 2009 at 22:04:23

A little difficult to advise, as I don't know what worksheet, worksheets or ranges that your current macro works on.

So I am starting from scratch and hope that you may be able to use this approach for your data.

I created a worksheet called "MultiPageSort.xls"
There is one worksheet named "Master" and I had 4 worksheets named Wk1 to Wk4. The worksheet names and the number of worksheets does not matter as far as the proposed macro is concerned.

Although it is typically better to link data together by the use of formulas, as you need to sort data which is a mixture of the same data on every worksheet (4 columns of name etc.) and different data on each worksheet (hours) I have opted not to link any of the data between worksheets.

As a result I created one sheet as a 'Master' with the 4 columns of names etc. and the other sheets have the same, but not linked, names with the bi-weekly hours.

The macro sorts all the data on all the sheets every time.

For this to work, there will need to be a rigorous approach to adding new names to every sheet, so that the sort macro running on each sheet results in the same names on the same rows.

To enter a new name (the 4 column data), select all worksheets (26 bi-weekly + Master), by clicking on the first worksheet name Tab, then hold down the shift key and then selecting the last worksheet name tab. This creates a 'group' and data entered on one sheet is entered on all sheets (also formatting applied to one sheet is applied to all sheets).

Now add the new name and associated data. It will be entered on the same row/ same cells on all worksheets.

Right click on a Worksheet name tab and select UnGroup sheets.

With a new name added, run the sort macro.

Sort Macro:
Add this code to a standard module (e.g., Module 1), not to a worksheet or the workbook object.

Option Explicit

Private Sub SortAll()
'sort data on all Worksheets in Workbook
Dim objWS As Worksheet

On Error GoTo ErrHnd

For Each objWS In Workbooks("MultiPageSort.xls").Worksheets
    With Selection
        .Sort _
        Key1:=Range("A2"), Order1:=xlAscending, _
        Key2:=Range("B2"), Order2:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, _
    End With
Exit Sub

End Sub

Note that the sort command is all one line, separated using the line continuation character '_' , for ease of viewing.

Note that I have used 'UsedRange' for the sort and this assumes that there is no extra data on each worksheet, just the name etc. and the hours information.

Also all worksheets have headings in Row 1 and data starts in row 2.

Link this macro to a toolbar button to make it easy to run.

When the Macro is run, all data is sorted, and you should find that the hours data remains with the appropriate names.

If you have any additional worksheets that should not be sorted, add a worksheet test before the objWS.Activate

Something like:
if obj WS <> Worksheets("DontSort") then
objWS.Activate ...

Also I suggest that you use the Master worksheet to show that all names are in the same place on each worksheet.

A formula based on the following should work:

...with your worksheet names and repeated on each row.

Just testing the last name is probably sufficient for this test.

I think that AND() will handle all 26 worksheets.

The Macro should not be run if any of the test formulas return "Error".

The macro has had only very limited testing, so you need to test it in your setup and with your data. You must ensure that you have suitable backups of the original data.



Report •
Related Solutions

Ask Question