Additional Info


  • Hot Topics

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. Last edited by RAF1982; Sep 4th, 2014 at 02:56 AM.

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

This thread is current as of September 4, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel