VBA code to SORT and run Subtotals after sort

Melbut
Melbut used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Should the sort be ascending or descending?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I am looking for the start date to go fro earliest to latest
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

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
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Author

Commented:
Thank you very much for this part of the solution. Excellent job in resolving my sorting and subtotaling problem.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you send me the workbook in an EE message?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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").

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you saying that you don't have a Type mismatch error any longer?

Author

Commented:
Im sorry what is EE message?  Unfortunately I can not send the actual workbook as my company automatically strips attachments.

Author

Commented:
I got it to work.  I had an error in column B which caused it not to run properly.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Take a look at this. If you want me to change which columns the various "totals" are in, let me know.
29136966d.xlsm

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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?

Author

Commented:
It would be nice to see it both ways.  Both definitely don't want any unnecessary work for you.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Ok, lets just go with the three outputs (All, Active, Expired)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this. I arbitrarily changed some values to 'Active' so that I could test that state.
29136966e.xlsm

Author

Commented:
Prefect.  It works like a charm.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial