I use an excel sheet to track shipments as they are processed and move through the facility, and I am creating a macro sheet to automate tasks. I don’t have any VBA experience and have been using YouTube to learn what I need as I go. I am stuck on two problems that I cannot figure out. I have searched for solutions to both these issues here and on other coding forums but still cannot get them solved.

In formatting the raw data, the shipments are split into multiple ranges then stored to variables (rngFly, rngTruck, etc) with the same header on each section, and with the total pcs and last row number of each section defined and stored to variables (LRowFly, totalFly, etc). Each section is sorted differently, so I want a button for each section to sort them. My first issue is that I cannot figure out how to set up my code so that range, LRow, and total variables persist after runtime in order to reference them with the sort buttons. Each section has different amounts of shipments each day, so I cannot use static range definitions. The second thing I am stuck on is that one of the sections is sorted by the cell color of column A, then next by the values of Columns B, C, D, and E. When I use Sort.Sortfields it seems to breakdown after the 3rd sort criteria.

For referencing the ranges created during run-time, I have all variables required for sorting declared as Public in the top of my code, then defined in the first sub-routine the variable is used. I have tried putting the sorting code into a subroutine, SortFly(), then assign that sub to the button on the sheet, and have also tried putting the code in the button click event, but get the object variable not set error with both methods.

For the sorting issue, I am testing with static range definitions until I get the above issue solved. I have tried applying the .sort function to both the worksheet and the range variable but cannot figure out the correct syntax for 5 sorting criteria.

Any code, or links to topics that would help, would be appreciated. Thank you in advance!