Hello everyone, I'm Steve, new here. Came for excel guru expertise. Have a question about creating a macro that will filter out 2 (changing) values on a filter.

I use a report that comes to me weekly with multiple tabs (always named the same) and I spend a lot of time formatting the report the same way each week to get the information out of it I need. Naturally I decided to create a macro to speed up the process and do a lot of it for me. Problem is, on these multiple tabs, the values I am looking at each week and filtering out are not the same week to week, or tab to tab.

As it stands now, I apply a filter to row 1 (column headings), and then filter out the highest and the lowest values in a certain column. There is always a 1, followed by 2, 3, etc, all the way up into the hundreds or thousands. I always filter out 1 and whatever the highest value is. Since there is not a way to select min AND max, my current workaround is to use Number Filters and use the "between" function specifying greater than or equal to 2, and less than or equal to 100 since most of the time the value I am filtering out is not less than 100. But this is not foolproof and could potentially mess up my report should a less than 99 come back, for example. The other workaround I was looking into is conditional format that would use some sort of formula to color coordinate based off the determined max value on each tab, whatever it happens to be, and then filter out by color.

I am not sure if this would be best route, or if my current workaround is the best solution? I haven't been able to find anything else that will work and am not necessarily an expert on all things excel to know the easiest way to get it done. That is why I am here asking for guidance. I was told there may be a way to insert an additional column with a combination of if formulas, but haven't gone that far yet.



And then my follow-up question is if it is possible to build into the macro a way to copy and paste the contents of each tab into 1 tab at the end. I want all info (all formatted the same, same columns, same headings) from each tab copied over to a compiled list tab at the end, but I am not sure if this is possible. If it is, my second question is it possible to copy a certain format at a time? For example, if each tab contains red and green text/font, can I copy all red text to 1 tab, and green to a separate. I was trying to play with this today and copy all (from tab 1) and paste into the compiled view tab at the end, but was not able to figure out going back to tab 2, and pasting the contents under the already pasted tab 1 contents without overwriting it. I was attempting to do something like CTRL down to find the bottom value and then paste under it, but I may have messed it up. Not sure if there is some paste append option that I am missing.


Sorry for the long read, but these are the 2 snags I am hitting in creating a macro that would save me a lot of time. I appreciate any assistance. Thank you for your time.