Complex Filtering & Copying With VBA (Need Help)

[ATurek]

Hey All,

I am attempting to create a sort of complex selection system with VBA and could really use some help. The primary goal I am trying to achieve(there are a couple of filters I need to create) is to find the most recent date a certain customer is mentioned so that I can copy some information from that particular line into a sort of summary sheet. The information I am filtering through is spread over multiple tabs which adds another level of complexity. I'm relatively new to VBA so this is particularly daunting to me as I am not very familiar with the syntax.

I have attached an example of what I would like the end result to look like to this post, please let me know if you have any questionsAny advice on coding and syntax to achieve this would be great.

VBA Filter&Summary Example.xlsm

Thanks for you help!
-ATurek

[david000]

Your "Example" workbook makes me question your authenticity.

[ATurek]

Well considering I would get in quite a bit of trouble if I used the actual names of the companies and such I figured I might as well have a little fun with it. Sorry if you did not find it as amusing as I did.

[p45cal]

In the attached (no vba/macro), I've manually: added a sheet, consolidated the three sheets to one database (a multiple consolidated ranges pivot table didn't work for me), created a pivot table, and chosen the Top 10 (using top 1) value filter in the Date Ordered column. The last column of the Pivot Table can be hidden but was necessary for the Top 10 filter to work.

The pivot table shows the same results as your sample.

I think most of this can be automated; is this the way you like to go?

The information I am filtering through is spread over multiple tabs which adds another level of complexity.

As an aside, it's much easier to have a single database like on the added sheet as your working database instead of multiple tabs - you can easily Autofilter the table for month by month data which look exactly the same as your existing sheets. In addition, as you're discovering, having the data on multiple tabs makes things difficult, for any sort of reporting.