VBA code to SORT and run Subtotals after sort

I have a spread sheet that has data in columns A - AA. The headers start on row 3 and data rows are 4 - 582.  The name of the sheet tab is call RAW_DAT.  I will be adding additional rows with data. I am not that familiar with VBA coding, but I would like a code to do the following:
1. Using a button to sort on columns F, B and I
2. Using a button to revert the sort back to original state
3. ability to subtotal on Column N after sort and undo subtotals
MelbutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
When you say
sort on columns F, B and I
Do you mean separately or do you mean together, and in any cade do you want the sort to expand to the A to AA?

If you could attach a sample workbook that would help.
MelbutAuthor Commented:
I apologize for my lack of clarity.  I believe together.  Yes, I do need the sort to expand to AA.  Unfortunately I am not able to upload the actual data due to company security reasons, but I have uploaded a file with the headers and some fictitious data.  I hope this helps.
License-Tracker.xlsx
Martin LissOlder than dirtCommented:
Should the sort be ascending or descending?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

MelbutAuthor Commented:
I am looking for the start date to go fro earliest to latest
Martin LissOlder than dirtCommented:
In your question you refer to the name of the sheet as "RAW_DAT" but in your sample workbook it's "RAW_DATA". My code assumes the name is "RAW_DATA".

The attached workbook only does the sort and restore of the unsorted data. It does that through a hidden sheet named "Undo" that I added.

Please check the sorted results to see if I have the order correct. Also please tell me more about the subtotaling you want to do.
29136966a.xlsm
MelbutAuthor Commented:
This looks great so far.  However, the sort needs to group all the project names together.  I guess to solve this I will have to forgo sorting on column B in order for this to happen.  Can we adjust that in the code or do you have another way to capture it.

As far the subtotaling I was looking for the change to happen under Column B Ref ID# and the subtotal to the Monthly price.  Can I have the entire subtotal row Bold and the subtotaled price itself shaded a light yellow and the font red.

Also, is there way to auto extract the Expired and Active data on to an Expired sheet and Active sheet keeping  the format of the sort and subtotal. This data is under column O - Deploy.
Martin LissOlder than dirtCommented:
Wouldn't sorting on 'F', then 'B' and then 'I' give you what you want?

Can you please provide a small, manually done, sample of what the subtotaling should look like?

And as for the extracting data part, let's put off doing that until the sorting and totaling is done.
MelbutAuthor Commented:
Here is what I am thinking regarding the subtotals.  I know I can manually run a subtotal on Column B which will give me the change under the Ref ID .  However, I am trying to automate as much as possible and do not want to have to manually use the excel subtotal  function each time; plus I need other users who are not familiar with excel to be able just click a button to review and then undo when additional information needs to be added to the spreadsheet.  I hope this makes sense.  This is a very visible spreadsheet and I need it to be easily manipulated by other users to perform analysis on. I am  open to any recommendations as well.
29136966a_subtl-sample.xlsm
Martin LissOlder than dirtCommented:
Please take a look at this version. I think it does the sorting and subtotaling that you want. If so, please accept this as an answer and then we can move on to the other things that you want. Seeing as though this is your first question you may not know how to accept an answer and so hopefully this will help.

Note that I froze the headings.
29136966b.xlsm
MelbutAuthor Commented:
The sort is working great, however, I definitely need the subtotal broken out by the reference ID.  

Ref ID #      CTR#      Quote#      Project Name      Total Price
1      CTR01116      NA-001      Corp Communications      4500
1      CTR01116      NA-001      Corp Communications      4500
1      CTR01116      NA-001      Corp Communications      4500
1      CTR01116      NA-001      Corp Communications      4500
1      CTR01116      NA-001      Corp Communications      4500
1 Subtotal            NA-001      Corp Communications      22500
2      CTR01116      NA-001      Corp Communications      4500
2      CTR01116      NA-001      Corp Communications      4500
2      CTR01116      NA-001      Corp Communications      4500
2 Subtotal            NA-001      Corp Communications       $13,500.00
4      CTR01116      NA-001      Corp Communications      4500
4      CTR01116      NA-001      Corp Communications      4500
4      CTR01116      NA-001      Corp Communications      4500
4      CTR01116      NA-001      Corp Communications      4500
4 Subtotal            NA-001      Corp Communications       $18,000.00
8      CTR01116      NA-001      Corp Communications      4500
8      CTR01116      NA-001      Corp Communications      4500
8      CTR01116      NA-001      Corp Communications      4500
8      CTR01116      NA-001      Corp Communications      4500
8 subtotal            NA-001      Corp Communications      $18,000.00
Grand Total Corp Communications $ 67,000.00
Martin LissOlder than dirtCommented:
OK, this corrects my omission of the Ref ID total lines. I see however that you changed the requirement for the grand total line from one grand total line to a grand total line per project name. I'll continue with that and other changes when you've accepted this, assuming of course that it's correct.
29136966c.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MelbutAuthor Commented:
Thank you very much for this part of the solution. Excellent job in resolving my sorting and subtotaling problem.
Martin LissOlder than dirtCommented:
Okay then for the grand total do you only want a "Grand Total" line following each change in Project Name or do you want a what I'd suggest calling a "<Project Name> Total" line following each change in Project Name and a "Grand Total" line like the one that's there now?

Also, do you want the total amounts to be yellow and red?
MelbutAuthor Commented:
When I copied the code into the module with my data that holds 582 row records I get a Run-time error '13' Type mismatch

If .Cells(lngRow, "F") = .Cells(lngRow + 1, "F") And _
    .Cells(lngRow, "B") = .Cells(lngRow + 1, "B") Then
Martin LissOlder than dirtCommented:
Can you send me the workbook in an EE message?
Martin LissOlder than dirtCommented:
If not then when it fails, tell me what's in Cells(lngRow, "F"), Cells(lngRow + 1, "F"),  .Cells(lngRow, "B") and Cells(lngRow + 1, "B").
MelbutAuthor Commented:
What you have is fine, however can just add a subtotal to the project name to capture the entire total for that project name group.

So in other words we currently have the total for each change under the Ref ID that is grouped by the project names.  Now I just need a total or grand total for the grouped projects themselves.  Hopefully this makes sense.

 I am thinking something like the below:

1 - NA001 - Corp Communications - $750.00
1 - NA001 - Corp Communications - $750.00
     1 Total - NA001 - Corp Communications - $1500.00
2 - NA001 - Corp Communications - $750.00
2 - NA001 - Corp Communications - $750.00                        
     2 Total - NA001 - Corp Communications - $1500.00
                         Corp Communications Total  - $3000.00
3 - NA001 - FIMs - $750.00
3 - NA001 - FIMs - $750.00
      3 Total - NA001 - FIMs - $1500.00
                          FIMs Total  - $3000.00
                              Grand Total - 6000.00
Martin LissOlder than dirtCommented:
Are you saying that you don't have a Type mismatch error any longer?
MelbutAuthor Commented:
Im sorry what is EE message?  Unfortunately I can not send the actual workbook as my company automatically strips attachments.
MelbutAuthor Commented:
I got it to work.  I had an error in column B which caused it not to run properly.
MelbutAuthor Commented:
The word "value" appear in column B on a few lines I assume causing the type mismatch.  Once I cleared that out and ran the subtotal it worked.
Martin LissOlder than dirtCommented:
what is EE message
  1. Click on my name that's to the right of my picture icon in any of my posts.
  2. Then under the green "Connect...", click 'Message"
Martin LissOlder than dirtCommented:
I don't want to get you in any kind of trouble but there are several sites like MediaFire that allow you to share files. You upload your file and it produces a unique URL. When you tell me what that URL is I can download the file.
MelbutAuthor Commented:
Unfortunately I am not able share the actual data information. But everything is working great so far. I realize it would make things a lot easier on your end. Just tried the site and it's blocked
Martin LissOlder than dirtCommented:
Take a look at this. If you want me to change which columns the various "totals" are in, let me know.
29136966d.xlsm
MelbutAuthor Commented:
Thank you so much!  This last solution you provided was prefect.  I would like to the subtotals highlighted to stand out a bit more, but other wise it's definitely providing what I need. I think this will do and we can move onto the second part of how to extract out on to separate tabs/sheet the Active and expired keeping the same format as the RAW_Data sheet with the ability to run the same functions (Sort and subtotal and undo).

BTW how can I give a rating of your service
Martin LissOlder than dirtCommented:
BTW how can I give a rating of your service
Thank you for asking. Once a year the Most Valuable Expert (MVE) award is given to a few experts based on voting by Experts Exchange members. The voting for 2018 is complete and the results will be announced in March. Think of me next year. Thanks.

How would you like the subtotals highlighted? Do you want them colored like this?
2019-02-24_06-39-46.pngIf not then please show me or describe what you'd like.

For the Active and Expired I could add two additional buttons like this
2019-02-24_06-47-33.pngOr when you press the original button I could have it ask what type of subtotaling you want (All, Active, Expired), or I'm open to any suggestion on your part.
MelbutAuthor Commented:
Thanks for the rating information and yes I will definitely keep you in mind.  

All your suggestions are fine.  Will this method extract to separate sheets keeping the format of the RAW_DATA sheet?
MelbutAuthor Commented:
It would be nice to see it both ways.  Both definitely don't want any unnecessary work for you.
Martin LissOlder than dirtCommented:
How about this? When you click the current 'Subtotal' button, the RAW_DATA sheet would remain unchanged but you would always get three output sheets  (All, Active and Expired), all in the same format as the current RAW_DATA sheet after subtotaling. That would also have the benefit of not having to have and 'Undo' button because the RAW_DATA sheet would not be changed. BTW, it would be less work than it sounds.
MelbutAuthor Commented:
The less  tabs is more ideal for me, and will it refresh the other sheets when new information is added or changed.  But willing to see what this looks like
Martin LissOlder than dirtCommented:
The less  tabs is more ideal for me
Why?
will it refresh the other sheets when new information is added or changed
In the design I just proposed, the three output sheets would be updated every time you pressed the 'Subtotal' button.

And while I said
BTW, it would be less work than it sounds.
this change would be harder for me to undo if you didn't like it, so I'd rather have you describe your ideal for the way you want this to work.
MelbutAuthor Commented:
Ok, lets just go with the three outputs (All, Active, Expired)
Martin LissOlder than dirtCommented:
Try this. I arbitrarily changed some values to 'Active' so that I could test that state.
29136966e.xlsm
MelbutAuthor Commented:
Prefect.  It works like a charm.
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.