Excel: Auto-Sort Macro for Multiple Sheet Workbook.


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.


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of September 04, 2014.


For more resources for Microsoft Excel