Auto-Sort Macro for Multiple Sheet Workbook.

RAF1982

New Member
Joined
Sep 4, 2014
Messages
12
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:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Just wanted to check back in to see if anyone can figure this out. Help is greatly appreciated. Thank you.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top