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.
Add this code to a standard module (e.g., Module 1), not to a worksheet or the workbook object.
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
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
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
if obj WS <> Worksheets("DontSort") then
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:
=IF(AND(Wk1!A2=Master!A2,Wk2!A2=Master!A2,Wk3!A2=Master!A2,Wk4!A2=Master!A2),"OK","Error") ...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.