update other workbook upon one updated

[prabhuduraraj09]

Hi Friends,
some timely help would be helpfull for me.
I have two workbook.
First workbook where i update status of every serice no.
example - i have list of service no. respectively i will update status as Yes or No
2568
65485
4545
6896
6548

workbook 2 has more data it is summary sheet, what i need is vba code
to find for this service no. in workbook2 if Yes in workbook 1, Then in workbook 2 status should get updated as Completed.
if No then Not completed.
If there status is blank in Workbook 1 then no need to update anything in workbook2.

So for all the service no. in workbook 1, status should get updated in workbook2 via vba code. upon clicking the button, i should be able to do bulk update of status change in workbook2.

i have attached sample file in sheet 1. underworkbook 1 i have listed sampl and at right side... i have mentioned as workbook2, this is how wrokbook2 has the data.

pls help..

[StevenM]

In workbook 1, which worksheet has the data?
In workbook 1, will the service numbers always be in column B?
In workbook 1, will the process (yes, no) always be in column D?

In workbook 2, which worksheet has the data?
In workbook 2, in which column will I find the service numbers?
In workbook 2, in which column will I find the process (completed, not completed) column?

[prabhuduraraj09]

In workbook 1, which worksheet has the data?
In workbook 1, will the service numbers always be in column B?
In workbook 1, will the process (yes, no) always be in column D?

In workbook 2, which worksheet has the data?
In workbook 2, in which column will I find the service numbers?
In workbook 2, in which column will I find the process (completed, not completed) column?

Thanks for the revert
Answer is,
workbook1
1. Sheet1 has the data
2. Yes service number will always in column B
3. Yes Process (yes,no) will always be in Column D

Workbook2
1.sheet has the data
2.Service number will be in column E
3.Now F column has the process which will be reflecting as "To be processed" in column K. with the help of vba code withe reference to the Workbook 1, now i should able to update Process in Workbook as "Completed , not completed) respective to Yes or no from workbook1.

[StevenM]

Copy this code into a standard module in Book1 (if you don't know how, ask)
If Book1 and Book2 are not in the same folder, then change sPath for Book2's path.
For example: sPath = "C:\Users\UserName\Desktop\Development"
Change the name of sFileName to the name of your Book2.
If it does not find a Service Number in Book2 it does nothing.

Please Login or Register  to view this content.

If you have any questions, please feel free to ask.

[prabhuduraraj09]

Hi Steve,

man you helped me at right time...thanking you wud be little. hey steve i have one more doubt,i have tested this code and it updating
book2 at Column F as completed / not completed. This is fine. addingto this,
now in book1 i have Date in Column C now this should get reflected at Book 2 at Column G only for (Completd ). For Not completed nothing should get updated.
i tried modifying your code but not successfull...

[StevenM]

Add the second line, after the first (or copy the code below and replace):

Please Login or Register  to view this content.

(a) Cells with a dot before it and nothing else belong to this workbook (the workbook where the code lives).
That is based on the statement: With ThisWorkbook.Worksheets(1)
(b) Cells with ws before it belong to the workbook which was opened.
That is based on the statement: Set ws = wb2.Worksheets(1)
(c) nRow is the loop counter for this workbook.
(d) rgCell.Row is the row of the item found in the opened workbook.
Once you know these four facts, all you need to do is add "G" to one and "C" to the other.

I added a few notes in the code to help you understand what is going on.

Please Login or Register  to view this content.

[prabhuduraraj09]

Hey you are the man steve...thanks much for the help. may be i need more help from you especialy in future haha!

[prabhuduraraj09]

Hi, i got a query in this. that is... if the service no. in book1 not available in book2 then can, some message box can be included. to tell me...what are all the service number which is not in book2???

[StevenM]

Do you want that to popup once at the end, or each time it happens?

[prabhuduraraj09]

Hi Steve,
Just once at the End. so i could note of those service no's.

[StevenM]

See if this works for you.

Please Login or Register  to view this content.