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: