Macro to select a data range then apply a custom sort

First I apologize for the long post that will probably need even FURTHER information that I didn't realize I need to include. Complete newbie & I have been researching this all day & have seen numerous posts with solutions for things "similar" to what I need. The problem is I've never even looked at VBA before today & I don't understand the code enough to modify it for my needs. I think some of the problem is also that I am working in an excel file that was poorly constructed before I took over managing it. I have attached the file with sensitive info removed for reference. The file is a list of short sales active with my company, grouped by branch, all on one sheet. I am looking for a macro that will dynamically select the rows for each branch & then apply a custom sort with 4 levels in the order: 1. Column K (A-Z), 2. Column J (Newest to Oldest) 3. Column I (Newest to Oldest) & 4. Column F (Newest to Oldest). For example, in my file the first branch, 2333, consists of rows 7-42.

I taught myself Dynamic Named Ranges today & was able to to create the most simple macro in the world to highlight the rows for the first branch (Branch 2333) using named range "_2333":

Please Login or Register  to view this content.

but the next step of applying the custom sort is where I get lost. I recorded a macro where I selected the data in the named range "_2333" then applied the custom sort & this is what it returned:

Please Login or Register  to view this content.

The problem with that code is it's using the static ranges instead of the dynamic named range. This doesn't work considering I'm constantly adding rows to the list. When I attempted to replace all the ranges in the above code with my dynamic named range "_2333" to get this:

Please Login or Register  to view this content.

I get the following message: "Run Time Error '1004': The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

When I hit Debug it Highlights ".Apply".

I may even be going about this completely wrong & there might be a much better way of approaching it I just don't know enough about macros. Any help would be HUGELY appreciated.

Hi - I came across this thread while looking for a solution to my Macro situation.

I recorded a Macro to sort based on YTD and assigned it to a button. I then recorded a Macro to sort based on YTD and assigned it to a button. These are both on the same sheet within a workbook. I realized that if a new row were added that data would not be included in the sort and that I also wouldn't be able to attempt to have this data from 5 sheets copy into my master document.

From reading threads I gathered I could go about this a couple of ways, but a dynamic named range might be the best to allow me to copy from these workbook sheets into my master.

I created the dynamic named range: =OFFSET('Rep Ranking'!$B$5,1,0,COUNTA('Rep Ranking'!$B$5:$B$4998)-1,58)

I had issues editing the Macros I previously created and after coming across this thread I modified and tried the one above.
It works pefectly for the first Macro to sort YTD, but when I copy and paste it and modify the column to sort by I receive a run time error 1004 stating application defined or object defined error and clicking debug highlights the word apply.


Any assistance would be greatly appreicated.

Working code:

Please Login or Register  to view this content.

https://www.dropbox.com/s/hweyj3txm3...nkSnapshot.png

cfieser,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.