Returning information from separate workbook based on cell text

**pdmkh** · 11-26-2010, 08:02 AM

I am wondering if there is a formula that will import/export specific row information from one work book to anoither based on specific text that is entered into a cell range? I have attached three test sheets to help clarify what am looking to do. Basically, whenever "DELAYED" is selected in column "H" in workbook "TEST CREW 1" or "TEST CREW 2" I need information from the row to populate in the designated area on "PAR TEST". Any suggestions on how to make this happen automatically ??

**davesexcel** · 11-26-2010, 09:01 AM

Place this code into the worksheet modules of the crew workbooks.

Please Login or Register  to view this content.

This code activates when a cell in Column H becomes "DELAYED". It then copies specific ranges in the row and pastes the copied cells into the first empty cell after the last cell in column A, so you may have to rethink your PAR TEST set up.

Anyway, it's a tool you can work on. and modify. Edit the cell ranges you want copied, merged cells will effect the code.

**MarvinP** · 11-26-2010, 09:02 AM

Hi pdmkh,

Without writing some VBA code you can get close to what you need by using an Advanced Filter on the Crew sheets and then cut and paste data over. Find the attached for an example. This doesn't automate the whole problem but gives you a start by filtering the Delayed to a single range.

**pdmkh** · 11-26-2010, 12:35 PM

Thanks for the help. I have loaded the code into the Crew work book, I need some direction on entereing the correct location/address of the "PAR TEST" work book. Thanks again and please have patience.
Set wb1 = Workbooks("PAR TEST")

**davesexcel** · 11-27-2010, 09:52 AM

Originally Posted by pdmkh

........... I have loaded the code into the Crew work book, I need some direction on entereing the correct location/address of the "PAR TEST" work book. .............
Set wb1 = Workbooks("PAR TEST")

What do you mean?

**pdmkh** · 11-29-2010, 09:37 AM

Thank you, thank you, thank you. This code is working. I originally had each work book opened individually on separate screens, when I entered "DELAYED" on the crew work sheet an error would appear in the code at "Set wb1 = Workbooks("PAR TEST")". I was trying to identify the issue later on a laptop and had both workbooks opened ontop of each other and the code worked. Any explanation on why this happens??. Also:
1. Is there a way to have the "PAR TEST" workbook update without being opened?(I could have it as another work sheet in the CREW workbook)
2. If the "DELAYED" text is changed to " DELAY COMP" can the copied text then be removed from the "PAR TEST". Sorry for so many questions but this is very close and would be a huge time saver for me.

**pdmkh** · 12-01-2010, 06:26 AM

1. Is there a way to have the "PAR TEST" workbook update without being opened?(I could have it as another work sheet in the CREW workbook)
2. If the "DELAYED" text is changed to " DELAY COMP" can the copied text then be removed from the "PAR TEST". Sorry for so many questions but this is very close and would be a huge time saver for me.