Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Auto-Sort Macro for Multiple Sheet Workbook.

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  2. #2
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    I wanted to also mention the data I'm wanting auto sorted is numerical. Thank you.

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    Any takers? Help is greatly appreciated.

  4. #4
    Board Regular
    Join Date
    Jan 2013
    Location
    Gulargambone NSW
    Posts
    772
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    RAF1982,

    With the linkage to your other spreadsheets via formulas, is there any reason we can not do it by code via copy and paste?

    What is the most complcated formula you have?

    Can you give an example of the data in "Sheet5" and how it is distributed to each of the sheets (what are the rules)?

    thx

    FarmerScott

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    Thank you for the reply, FarmerScott.

    I may have used the wrong terminology above so I apologize for the confusion.

    Sheet 5 has no formulas, it is a sheet where revenue and profit numbers are manually entered. The columns are separated by Monthly Net Revenue, Monthly Net Revenue YOY, Monthly Pofit, Monthly Profit YOY.

    As this data is manually entered into sheet 5, it auto-populates to sheets 1,2,3,4.

    What I am wanting is Sheet 1 to auto-sort in ascending order all data by Monthly Net Revenue.
    - The first sheet's data range is A6:M50 with the sort range being F6:F50
    Sheet 2 to auto-sort in ascending order all data by Monthly Net Revenue YOY.
    - The second sheet's data range is A6:M50 with the sort range being G6:G50
    Sheet 3 to auto-sort in ascending order all data by Monthly Profit.
    - The third sheet's data range is A6:M50 with the sort range being K6:K50
    Sheet 4 to auto-sort in ascending order all data by Monthly Profity YOY.
    - The fourth sheet's data range is A6:M50 with the sort range being L6:L50

    Please let me know if that better explains what I am looking for. I appreciate your time and help. Thank you.

  6. #6
    Board Regular
    Join Date
    Jan 2013
    Location
    Gulargambone NSW
    Posts
    772
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    RAF,

    No issue with your explaination, it just helps to clarify some points as the person with the problem will have more understanding of the issue and it helps to get a clearer picture in our heads.

    How exactly does the data from Sheet 5 go out to the other sheets (via formula)? What is the formula?

    Do you want the data to be updated as soon as you change data or are you happy to run code once all the data in sheet 5 has been updated?

    FarmerScott

  7. #7
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    Thanks again for the reply, FarmerScott.

    In each Sheet that is receiving data from Sheet 5, I have a an = formula in the cell to auto-populate that data. For example, if I have data in cell F4 on Sheet 5 that I want in cell F6 on Sheet 1, the formula would be ='Sheet 5'!F4. That is how I am pulling the data to the other sheets.

    Regarding when it is updated, the data entered into Sheet 5 will be entered at different times so it would be ideal to have it update as soon as data is changed.

  8. #8
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    Just wanted to check back in to see if anyone can figure this out. Help is greatly appreciated. Thank you.

  9. #9
    Board Regular
    Join Date
    Jan 2013
    Location
    Gulargambone NSW
    Posts
    772
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    RAF1982,

    Back again, sorry for the wait but work have been a bit full on.

    What are the rules for the data to go to each page? Is it based on text, values, etc? This is important. Can you give a copy of your sheet5, using the table function once you have clicked on "Go Advanced".

    Just so we are on the same page with this code, I am thinking of getting the code to automatically distribute the data to the other sheets. Then there will be no formulas that can create problems. We can get the code to run everytime you enter data, it will update you other sheets and then auto-sort.

    FarmerScott

  10. #10
    New Member
    Join Date
    Sep 2014
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-Sort Macro for Multiple Sheet Workbook.

    Hey FarmerScott -

    I tried doing an advanced reply with the table but it won't show you exactly what I've done. I've created a screencast video for you to show you exactly what the spreadsheet looks like, you can view it here:

    http://screencast.com/t/JWBws0ricu8d

    This will show you what Sheet 5 looks like and how it auto-populates data into the other sheets. I don't know if creating a code to transfer data over is feasible based on how Sheet 5 is structured. You'll see it in the video.

    Thanks again for your help.
    Last edited by RAF1982; Sep 18th, 2014 at 05:24 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •