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.

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.

Last edited: