Excel/Follow up excel
I'm afraid you assume I am more handy with Excel than I am. If you want to still give this another try...
Your point about A:F doesn't help. I already highlighted all the spreadsheet with my mouse and tried to copy it, but got the message that it was too large for the clipboard. I don't know any other way to copy stuff. You said .Range("A1 etc.").Copy.
I don't know what ".Range" means. Is that some kind of copy tool? As I said, the only way I know to copy is to highlight with mouse and then right click Copy, and the range would be A1:F6516, but again, that is too large for the clipboard.
Now this is just for your initial advice. I don't know anything about macros.
Art - I'm sorry! I didn't mean to leave you out in the cold.
I created the macro for different reasons. Most importantly, I could not see a way to get the results you want manually. You can run AutoFilter by pressing the button, but without being able to specifically filter out the Grand Total and only after you'd performed the cumbersome copy & paste. And ultimately, the AutoFilter won't work unless the SubTotal is modified and then the SubTotal doesn't work right again. Which is why I wrote the macro to handle all of these problems.
If you are not comfortable with macros this might not be the right one to start with. I will have to refer you to another Expert, hopefully they will be able to see a solution I cannot. If you want to give a shot at using the macro, read on --
You do not have to copy or filter the data, the macro will do that. You do not need to understand the entire macro, just the two things you may in the future need to modify:
1. The size of your table changes. Anywhere you see "A:C", which just means columns A thru C, replace with the new size of your table: e.g. "A:F" or "A:B". Columns only, it doesn't need to know how many rows you have, it's just going to check them all. I tested this on a table with 6 columns and over 6,000 rows. A brief delay, but certainly better than doing it by hand.
2. You add or remove columns. Field:= is refering to a column (using a number instead of a letter, for some reason) but the number is the same as the column, so 1=A, 3=C, etc. In this macro Field:=3 is looking for any amount greater than 100 in column C. If for whatever reason the amounts are now in column E instead of C, just change Field:=3 to Field:=5 (5=E).
To run the macro, either go to your Developer tab and choose Macros, Run or right-click on the sheet tab, choose View Code, and hit the green triangle/arrow on the toolbar (then close the window with the red X and you'll be back in your worksheet).
Again, I did not mean to overwhelm you, and I hope you find the solution you need - Andrea