Moving Rows from Workbook 1 to Workbook 2 if Column H has "X" value

[Spirographed]

Excel 2016

I'd like to make a list in Workbook 2 from data in Workbook 1, and be able to sort that list.

For all rows in Workbook 1, if the cell in Column H has an "X" in it, I'd like to copy Column A-K of that Row to Rows sorted First by Column C (Descending) and then Column E (Descending) in Workbook 2.

I've pretty basic knowledge of Excel and am not sure if this is done with If/Then statements, or a Macro.



Workbook 1 is named 'Master Maintenance.xlsx'

Workbook 2 is named '2017 Lawn Maintenance List.xlsx'

[swade730]

Can you attach a sample workbook with data please?

[Spirographed]

No problem.

[CAntosh]

The walkthrough (steps 1-11) in this link should do the trick:

http://www.ashishmathur.com/dynamica...et-to-another/

Note that the "X" is case sensitive, so enter a capital X in step 8. You'll also get a chance to sort by your two columns prior to step 9. If done correctly, you'll just need to hit "refresh" on your filtered sheet to update the filtered sheet once you've made changes to your main data table. The walkthrough is not my own work, but I gave it a test run with your data and it seemed to function as requested.

EDIT: It's also worth noting that the instructions are for a sheet to sheet relationship, but it works just as well with two different workbooks.

[djbomaha]

Hi Spirographed,

Here is a workbook that if it is put in the same folder with the two files you are coordinating has code to do what you are describing. The code fires when the attached workbook is opened and will prompt you if you want to continue, so you can say "No" and then hit <ALT> F11 to view the code to give you some ideas or let it run to see how it works. It will only modify the 2017 Lawn Maintenance List.xlsx file, it leaves the other one unmodified.

Hope that helps,

Dan

[Spirographed]

The walkthrough (steps 1-11) in this link should do the trick:

http://www.ashishmathur.com/dynamica...et-to-another/

Note that the "X" is case sensitive, so enter a capital X in step 8. You'll also get a chance to sort by your two columns prior to step 9. If done correctly, you'll just need to hit "refresh" on your filtered sheet to update the filtered sheet once you've made changes to your main data table. The walkthrough is not my own work, but I gave it a test run with your data and it seemed to function as requested.

EDIT: It's also worth noting that the instructions are for a sheet to sheet relationship, but it works just as well with two different workbooks.

Hmmm...When I get to the last step, and click Finish, it loads some info into the Query window behind the "Finish" window, but doesn't actually finish. I can just keep clicking "Finish" without it doing anything else.

[Spirographed]

Thanks! I'm not sure it's for me though, because I will be doing this with multiple worksheets and columns, so I'll need something like the 1st suggestion where I can replicate it.

[CAntosh]

Hmmm...When I get to the last step, and click Finish, it loads some info into the Query window behind the "Finish" window, but doesn't actually finish. I can just keep clicking "Finish" without it doing anything else.

Hmmm... I can't get my Excel to replicate your error. Maybe try the whole process again from scratch? Double check that the named range is =Sheet1!$A$1:$R$75 (in your sample). In the "Query Wizard - Filter Data" step, use the dropdowns to select "equals" and "X" to ensure that there aren't any syntax issues. Beyond that, I don't use queries often enough to diagnose what's causing the hiccup. Maybe someone else can weigh in?

[Spirographed]

Redid the whole thing, and noticed a difference right away. For some reason it was making an empty table the first time. It works! Thank you so much for your help!

[CAntosh]

My pleasure, good luck!