MACRO Transfer entire row based on a table in a third sheet/tab

Hi guys,

Hope all is well with everybody!

Need help -

I have three sheets (please see attachment)

Basically, I would like to transfer NON US under the Dom tab to Intl. And I've already handled that using

Please Login or Register  to view this content.

My problem is those in Canada, for instance the Toronto, ON. As stated in my code, I base my transferrance on the State Column (Column B) And since Toronto, ON is <> 0 so it stays.

I made the US State Abbrev tab to try to make use of vlookup, find, etc but I have no idea how to use them in Macro. I tried but never did succeeded. Evidently.

Any help is very valuable. I'm looOosing me mind reading people's codes. (_ _!!!)


Thanks guys (in advance)

More power!

Kat

Oh and by the way, I am also open to a solution whereas States are vlookup/find in my third sheet (US State Abbrev). But any help will do

Thanks again!

eastydie,

I put in a formula in Column C of the Dom sheet
Formula: =IF(COUNTIF('US State Abbrev'!$B$2:$B$51,Dom!B2)>0,"US","Non-US")

The formula checks if the State Abbrviation exists in the table. If so, it populates the cell with "US" otherwise it populates the cell with "Non-US"

Using column C, I created a macro to move all Non-US items to the Intl sheet:

Please Login or Register  to view this content.

I've attached a modified version of your excel file that contains the changes

Hope that helps,
~tigeravatar