VBA Code for populating cells from one Work Book A to another Work Book with condition

[ray.kanata]

Hi,

I don't know much about VBA but know that VBA is an extremely rich programming language.
It will be great if someone help me and write a code to solve my problem.

I have 2 Excel workbooks ( A and B) at different locations.
Excel workbook A has "Master Sheet" having unapproved projects list and workbook B has three sheets (Yes_Sheet, No_Sheet, Hold_Sheet)
Both workbooks have same type of tables with same format(Please see attached workbooks)
The only difference is that in workbook A there is an extra column (heading: Decision) with drop down list (Yes, No, Hold)

What I want:
1- when we select "Yes" from drop-down list under Decision heading in Master sheet of workbook A, it should transfer all data (from "Project Name" to Column 10 except Column 2 and Column 4) of first project from "Master Sheet" in workbook A to Yes_Sheet in workbook B.

2- when we select "No" from drop-down list under Decision heading in Master sheet of workbook A, it should transfer all data (from "Project Name" to Column 10 except Column 5 and Column 9) of first project from "Master Sheet" in workbook A to Yes_Sheet in workbook B.

3- when we select "Hold" from drop-down list under Decision heading in Master sheet of workbook A, it should transfer all data (from "Project Name" to Column 10 except Column 5 and Column 9) of first project from "Master Sheet" in workbook A to Yes_Sheet in workbook B.


Each time when we select any one option from drop down list (Under Decision field in Master_Sheet of workbook A), it should transfer/populate the project data in next available row of required sheet (Yes_Sheet, No_Sheet, Hold_Sheet) of workbook B.

I would like to thank you all in advance.

Thanks

[ranman256]

Shouldnt step 2 copy to Wb-B NO sheet?
and
step 3 copy to Wb-B HOLD sheet?

[ranman256]

IF this is true (what i posted above), then enter the VBE screen,
in the right project screen, in the VBAProject of your MASTER workbook,
dbl-click on ThisWorkbook,
on the code screen, paste this code:

Please Login or Register  to view this content.

[ray.kanata]

Thanks Ranman256! I really appreciate your time......

I tested and it's giving me error.
See attached screenshotError.JPG

[ray.kanata]

Still waiting....

[ranman256]

hmm, its possible that you dont have it open.
or
just type in the full name instead of variable
Workbooks("WorkBook_B.xls")
or is it .xlsx?

[ray.kanata]

Hi Ranman256!

You are excellent in VBA
Now I understand the error.
It worked with: Workbooks(kBwb & ".xlsx")

It will be great if it copy when Workbook_B is closed.....Is it possible???

Thanks for your time

[ranman256]

no, you cant write anything to a file unless its open.

[ray.kanata]

Thanks!

I just notice that it also copy the drop down list.
I don't want to copy that drop down list from Workbook_A as it is just for decision in Master Sheet of Workbook_A.
See attached

Once again thanks for all you help

[ray.kanata]

Thank you very much Ranman256!

It's solved

[ray.kanata]

Hi Ranman256!

Why my VBA Code keeps disappearing ?

I have a code that auto save file in xlsm in another location but the above code in "Thisworkbook" keep disappearing.

Hope you can help me

Thanks

[ray.kanata]

Hi Ranman256!

Yes, I also tried to use the same code in particular sheet instead of pasting in "Thisworkbook", it didn't work.
is there anyway I can use same code in specific sheet.

thanks

[ray.kanata]

It's working fine but I want the code in master data sheet in workbook_A

Thanks

Code with files attached:

WorkBook_A.xlsmWorkBook_B.xlsx

[ranman256]

WorkBook_A.xls

update working for me.

[ranman256]

REMEMBER, you cant save vb code in a XLSX file. IT MUST BE .xls , OR .xlsm

[ray.kanata]

Thanks Ranman256! I appreciate ur care and time...Sorry I couldn't explain well

You are right it's working fine but whenever I save it to another location by using "save as" it disappears. I am thinking that if this code is pasted in Master Sheet of workbook A, it (code) will be not disappear.

See what I want in pic attached
What I want.JPG

[ray.kanata]

Thanks Ranman256! I appreciate ur care and time...Sorry I couldn't explain well

You are right it's working fine but whenever I save it to another location by using "save as" it disappears. I am thinking that if this code is pasted in Master Sheet of workbook A, it (code) will be not disappear.

See what I want in pic attached
Attachment 401453

[ranman256]

are you saving it as .XLS?

[ray.kanata]

Nop! I am saving as .XLSM

I was thinking if we paste the same code in "Master Sheet" of Workbook_A instead of "Thisworkbook" of Workbook_A, it will solve the problem.
My problem is I don't know how to modify the current VB code for "Master Sheet", so it should work same but pasted in "Master Sheet".

Can you modify it plz?

Thanks

[ray.kanata]

Plz see attached file.WorkBook_A (3).xlsm

[ranman256]

There is NO CODE in this wb. I pasted and it works fine.
WorkBook_A (3).xlsm

It doesnt matter what the workbook is named,
nor what the sheet is named.
The code MUST be put in THISWORKBOOK.