Fill Columns based on open date

[Thainguyen]

Hi All, I need help on this. I am currently looking for a formula or VBA code to copy and paste all information from column A to M and O to S columns based on the N column open date from workbook1 to workbook2.

There will be two scenario:
1. If the column N in workbook 1 have a specific date (Example 10/2/2018) the formula or VBA code should bring all data from column A to M and O to S in the workbook 2.

2. If the column N in workbook 1 have a word (Done or done). the formula or VBA code should ignore that row. also, If the date in workbook 1 have been changed to Done or done. The workbook2 should remove that data out. Please help.

[DocAElstein]

Hi Thai,

_a) It is not too clear to me what you mean by “….open date…” ?
Possibly you mean the current date at the time that the workbook is opened ?

I expect to do something like what you want, a formula may be possible. This would probably be a quite complicated formula. I do not personally know how to do this as I do not have much experience with formulas.

_b) It is very easy to write a VBA code which , based on the contents of a cell , copies data from one workbook to another. There are many, many ways to do this.
One basic approach would be as follows:
For your scenario 1. ….If the column N in workbook 1 have a specific date (Example 10/2/2018) the formula or VBA code should bring all data from column A to M and O to S in the workbook 2. ……
Loop down column N in workbook 1. At each row, you check for the value in the cell . If you get a match to the value which you are looking for ( in your case the “specific date” ) , then the required data would be copied over to the next free row in Workbook
For your scenario 2. …. If the date in workbook 1 have been changed to Done or done. The workbook2 should remove that data out…..
A second looping would be done in the code to check both the data in Workbook 2 and Workbook 1 for a match in row data for the columns A to M and O to S AND a Done or done in workbook 1. If you get a match in the data for the columns A to M and O to S AND a Done or done in workbook 1 , then the entire row would be deleted from workbook 2.

_._________________

If you still need help with a specific example, then I think it would be best to
_a) explain what you mean by “….open date…”
_ b)(i) provide us with 4 sets of data, ( 2 pairs = 2x2 = 4 ) ( These can be in 4 workbooks or 4 worksheets in one workbook ). This data should show us the
scenario 1. of workbook 1 and workbook 2 - one pair of data
and
scenario 2. of workbook 1 and workbook 2 – another pair of data

( The developed code would be just one single code to work for both scenarios: the same code should work for both pairs of data )

An alternative to this b(i) would be :
_ b(ii) Privide one data example for workbook 1 and two data exampes for workbook 2: a "workbook 2 before" and a "workbook 2 After" : "workbook 2 before" would be the situation just before running a code : "workbook 2 after" would be the changed situation in workbook 2 which you would want based on the data in workbook 1 and "workbook 2 before". Make sure you chose the test data carefully in order to test all possible scenarios.


_.____


Alan

[Thainguyen]

Hi Alan,
I got all figure out. Thank you

[DocAElstein]

Hi Thai,
Good to hear from you and that you solved your problem. Thanks for leting us know.
Alan
P.S. Incidentally, when you solve your own problem you can always post your solution if you want to.
Then others with a similar problem or requirement who may see the thread on a search in the future may benefit