VBA Copy & Paste Based on a Condition in Another Column

[Ekazumon]

Hello Everyone,

I was looking around the threads to see if I could find a problem similar to mine. Unfortunately, I was unable to find exactly what I was looking for. So, here I am.

Background:
I made an excel sheet to display data by location by using the unique item IDs. I would sort the data extracted from SQL(raw export) to a raw data tab. After several formulas, the item ID is then associated with a particular location (warehouse) as denoted by the column: location. Then, I group up the item IDs via location via sort. I then copy the corresponding item IDs into the matching tab based on the sorted location. I then make each tab a unique workbook and send them to the appropriate individuals.

I have attached a heavily simplified version of the workbook with dummy data.

Issue:
I have been doing this without any problem using my methodology. However, this task is going to be passed down to someone who is technically challenged. I was told by my manger to make this automated as much as possible. Preferably with a vba that can automatically paste the data into the appropriate tabs.

My issue is that I am unaware of how to do a vba copy & paste to another sheet with a condition that is located in another column. My knowledge only extends to if the condition is in the same column that is being copy and pasted. So in this case, I need all Item IDs that are associated with warehouse 1 in the column location to copy and paste into the first column under the tab, warehouse 1.

Alternatively, I successfully created a lookup formula that can auto fill the data. However, it leaves spaces filled with N/A between the header and data if the number of rows is less than number of corresponding Item IDs. Because
of this, I was told that this would not be an appropriate solution even though you would only need to delete the rows with N/A.


Any insight or methodology to solving this problem would be greatly appreciated. Thank you!

[austenr]

if you decide to go with the lookup (VLOOKUP is assumed) to suppress the #N/A try this:

=IFERROR(VLOOKUP(A1,Table,2,False), "Not Found")

You can also just use " " instead of the message.

Where IFERROR will suppress the #N/A

Hope this helps

You can also use IFNA in some versions.

[Rob342]

Try This
Many ways to do it this is one way
See attached

Rob

[Ekazumon]

Thank you for your help. Your solution is simple and exactly what I was looking for. As you noted, there are many solutions to this problem. Unfortunately, I was struggling because I was limited in the types of solutions that I could implement. I am fortunate that there are people like you to help! Your assistance is greatly appreciated.

[Rob342]

No problem Happy to help

Rob

[Ekazumon]

Thank you for your insight. I apologize, but I seem to have neglected a limitation. The only reason why I didn't take this approach was because it would leave empty rows. I attempted to solve this problem by creating a vba that removes cells with #N/A in them (or in this case blank rows). The issue is that my manager only wants one button on the sheet. Honestly, I don't understand why hitting one button then another is that complicated. I am sure there is a way to do this automatically: make the table automatically match the number of need rows. However, at this time, I think VBA is the more ideal solution.

Luckily, Rob342 may have a solution based on my limitations.