Solved: Automatic Sort After Data Change

[zoom38]

I need help sorting data after a change is made. What I am working on is a monthly schedule (28 days) for work and am trying to break it down to weekly schedules. If you look at the attached file worksheet "Squad 1" and "Squad 2" are the 28 day schedules. I have the employee name and shift that they are scheduled copied to the worksheet "Tables" and separated into shifts. I then have each day and each shift sorted first by shift rank and then by employee name. This amounts to 112 different sorts on "Tables" worksheet. My problem is that if there is a change in an employees shift on "Squad 1" or "Squad 2", the data is not automatically sorted in the "Tables" worksheet which leads to 112 manual sorts all over again or at least a new sort on the day of the change. Can anyone show me how to make the data sort automatically (refresh after change in data). I do not know how to build macros and I don't know VBA. I've looked at other related posts on sorting but they don't seem to apply. I have basic programming experience although 15 years ago but if someone could show me how to have two days for each shift sorted on "Tables" automatically I should be able to work from there and do the rest (I think).
Thank you in advance.
Gary

[Ken Puls]

Hi there, and welcome to VBAX!

You know how to do all these sorts manually, correct? How about we teach you how to get the answer you need? I or someone else can certainly help you out if you need it though.

Excel has an awesome feature for learning: The Macro Recorder. It catches the step by step of exactly what you tell it to do. So try this:

-Plan out your first two sorts carefully. Run through the actions a couple of times so that you don't have any extraneous navigation clicks or anything.
-Go to the Tools Menu, choose Macro --> Record New Macro
-Give it a nice name and choose to store it in "This Workbook" (So it is available in this file every time)
-Assign it a shortcut key if you like
-Click Okay. You'll probably see a new toolbar pop up, but don't worry about it if you don't.
-Perform your sorts carefully
-When you're done, Go to the Tools Menu --> Macros --> Stop Recording
-Now press Alt+F11 to get into the Visual Basic editor
-You should see a windows looking explorer on the left side. If not, press Ctrl + R to get it
-Navigate it until you open up "Module1". It will be in VBAProject(RVSD_MonthlySchedule3.xls)/Modules/Module1. Once you double click Module1 you should see a code pane open up with some code in it.
-Copy everything in that module and paste it here if you'd like someone to look at it.

Truly, that should give you the steps to do the first two. You could record all 128, but it might be an issue, as that's a LOT of clicks. Also, you may want to post the code here anyway, to learn what isn't required. The Macro recorder, while useful, does tend to add a lot of extra garbage that you don't need.

I hope this helps a bit.

[zoom38]

Thank you for the quick lesson on creating macro's. It works great. One question that I have now is why does the macro only work when I have the "tables" worksheet active. All of the sorting is done on the "tables" worksheet. If i'm on one of the other worksheets and activate the macro it messes up data.

I have it set up with a Main module which calls the four sorting modues so one shorcut key runs all of the sorting at once.

Here is some of the code for one of the modules:
Sub First_Shift_Sort()
'
' First_Shift_Sort Macro
' Macro recorded 1/10/2006
'
'
Range("A4:C18").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("D4:F18").Select
Selection.Sort Key1:=Range("F4"), Order1:=xlAscending, Key2:=Range("D4") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("G4:I18").Select
Selection.Sort Key1:=Range("I4"), Order1:=xlAscending, Key2:=Range("G4") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

[austenr]

Put this line of code in front of your first Range selection:

[VBA] Sheets("Tables").Select [/VBA]

You have to tell the macro what sheet you want sorted. By default it assumes you mean the current sheet.

[zoom38]

Thank you, that worked great. One more question, how do I make it so after the macro completes it will bring me back to where I was when I activated the macro. Currently after I activate the macro and the macro completes it puts me on the tables tab, I would rather it take me back to where I was when I activated it.

Thanks
Gary

[Ken Puls]

Hi Gary,

Give this a shot:

[VBA]Sub test()
Dim rngStart As Range
Set rngStart = Selection

'Your code goes here

With rngStart
.Parent.Activate
.Select
End With
End Sub[/vba]

HTH,

[zoom38]

Thankyou, worked great.
Problem Solved.

[mdmackillop]

Hi Ken,
I've not used "Parent" and although I see the logic, I've found that code returns to the original selection without this line, even if changing another workbook. eg.
[VBA]Sub test()
Dim rngStart As Range
Set rngStart = Selection

Workbooks("colours.xls").Sheets(1).[A10].Interior.ColorIndex = 6

With rngStart
'.Parent.Activate
.Select
End With
End Sub
[/VBA]
Are you aware of circumstances where this won't happen, or is it just "good practice"
Regards
Malcolm

[Ken Puls]

Hi Malcolm,

If your sheet is not active and you try to select the cell in it, Excel will bomb with a runtime error. I always make it a practise to activate a sheet before I select anything.

Activate a cell on Sheet 1, then run the following. You'll see what I mean:

[vba]Sub test()
Dim rngStart As Range
Set rngStart = Selection

With Worksheets(2)
.Activate
.Range("A1") = "Hi!"
End With

With rngStart
'.Parent.Activate
.Select
End With
End Sub[/vba]

Then try it without the parent line commented out.

[mdmackillop]

Thanks Ken,
I guess I've always reactivated with the original sheet name. "Parent" should save me some typing.
Regards
Malcolm