filter table and copy unique value to mulitple sheets

hi everyone,

I am trying to build a macro that will loop through filters and copy the unique values to sheets based on the filters. I've seen a few codes based on unique values and copy but can't seem to find one matches my needs.

example:

sheet 1 - in cell A1 I have a list of countries.
A1 - Canada
A2 - USA
A3 - Asia
A4 - Europe

Sheet 2 - this is my data tab with a table. my Table range is from D5:O5. column J is countries

Sheet 3 - Canada
Sheet 4 - USA
Sheet 5 - Asia
Sheet 6 - Europe

What I'm trying to do is on sheet 2 filter column J from Sheet 1 and then copy the unique values of column E and paste it in A1 of the corresponding sheet.

ideally a for loop that will check how many countries and then filter each one in sheet 2 and copy the results to the correct sheet.



any help would be appreciated.

can you post an example sheet to work with?

You don't need, nor would it be better, to use VBA for this. Your country sheets (like Canada) can be produced with Pivot Tables.

You could change the Filter value to see other countries, don't really need to make a sheet for each possible country (but you can if you like).

Originally Posted by matrix_xrs

I would prefer to do it through vba as I'm doing more things with the data afterwards. I have sumif and sumproduct formulas. using a pivot limits me on what formula I can use.

Well, you just have to direct your formulas back to the original data set. Would be easier to show this with an example than to try and convince you in a message.

But ... yes, you can do what you are wanting in VBA. See attached.

I didn't use the Country filter, I used the list of Countries on the 'Country' sheet. To do what you ask is possible... but: what you want is EXACTLY what a pivot table does.


Here is my advice: You should change what you are doing to work with the native features of Excel. Instead of working Excel around your personal style.There is no reason that the 'other calculations' you mention have to interact with the pivot tables. It is possible just refer to the underlying data set.

If that is no good, I suggest you start googling how to adapt my code to do whatever you need :D

At some point you just have to stop helping someone trying to reinvent the wheel.