Auto-Sort Macro for Multiple Sheet Workbook.

[RAF1982]

Hello Everyone... I have been working on trying to figure out a specific Macro for the last 5 hours and can't seem to get it right. I have searched this forum and can't find anything that specifically relates to what I am trying to do so any help would be greatly appreciated. Here is what I am looking for:

I have a 5 sheet workbook that I am using to record sales data for a sales region. 4 out of the 5 sheets pull data from 1 specific sheet (the 5th sheet). Those 4 sheets have formulas entered in each cell to pre-populate data from the 5th sheet so there is no manual entry on any of those 4 sheets. Manual entry only occurs on the 5th sheet.

Each of the 4 sheets data range is from A6:M50 and that will never change. What I am looking to do is have each sheet auto-sort in ascending order as data is entered into the 5th sheet. When the data is entered, I only want a specific column to sort per page and each row needs to sort with the column.

The first sheet's data range is A6:M50 with the sort range being F6:F50
The second sheet's data range is A6:M50 with the sort range being G6:G50
The third sheet's data range is A6:M50 with the sort range being K6:K50
The fourth sheet's data range is A6:M50 with the sort range being L6:L50

I attempted the following code and it worked only when I manually entered the data on the sheet the code was entered but it's functionality went away when I entered the formulas to pre-populate the cells with data from the 5th sheet. This is the first time I've tried Macros so I could be doing it completely wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Range("A6:M50").Sort _
Key1:=Range("F6:F50"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If

End Sub

I am guessing I need to have a separate code for each sheet so the correct range is being sorted, right? If so, what would the code be and do I just enter it by selecting view code on the specific tab and pasting it in?

I am using Excel 2010.

Again, any help would be greatly appreciated. Thank you for your time and help.

 

[farmerscott]

Try this code within your code. Put it at the end.

Dim ws As Worksheet


For Each ws In Worksheets
If ws.name<> "Sheet5" then
ws.Protect Password:="ABCD"
Next ws

If you need make changes on sheets 1-4 then you will need to put (and run) these codes in a standard module. Otherwise the sheets will be continually protected.

Sub unprotect()
Dim ws As Worksheet


For Each ws In Worksheets
If ws.name<> "Sheet5" then
ws.Unprotect Password:="ABCD"
Next ws

end sub

Sub Protect()
Dim ws As Worksheet


For Each ws In Worksheets
If ws.name<> "Sheet5" then
ws.Protect Password:="ABCD"
Next ws

end sub