VBA or Macro to Copy/Paste uniques value between 2 worksheets

[grotz497]

Hey there-

I've been searching around and found bits and pieces of what I need to do but can't quite put it together the way I want it to work. Here's what I'm trying to accomplish:

At work I manage a base of accounts that fluctuates every month. I’d like to have Sheet1 contain a master list of accounts I manage and have subsequent worksheets contain the monthly updates of my accounts (paste an exported report into the worksheet each month). Then I’d like to ID the accounts that are in the monthly update but not in Sheet1 then copy/paste the whole row of the new account into the first blank row in Sheet1.

The columns I'm working with are as follows:

A: Parent
B: Child
C: Customer Name

We would identify the new account by looking at Column B: "Child" in Sheet1 and Sheet2. If the number in Column B in Sheet2 (the monthly update) does not appear in Column B of Sheet1, I need to copy the whole row from Sheet2 and paste to the first empty row in Sheet1.

I've attached a workbook that's a much simplified version. Thanks in advance!

[JieJenn]

Try

Please Login or Register  to view this content.

[grotz497]

That works great thank you!

When one of my coworkers saw what I was doing and I explained, she came up with a request...

Is it possible to use that same Column B value to look in Sheet2 and if it finds a dulpicate value copy everything in Columns D-Z and paste it into Columns D-Z in the corresponding row in Sheet1?

[JieJenn]

What do you mean? Can you give me an example?

[grotz497]

Gladly! If Parent Acct Number, Child Acct Number, and Customer Name are in Columns A,B,C respectively, Columns D,E,F,G,H etc have information like contact person, date last contacted, monthly recurring charges, email address, phone number, and additional notes.

Because we've been updating our base of accounts each month, we end up with a different spreadsheet for each month and it's a long process to integrate all of those notes. The thought was after we use the first solution you provided to see if we have any new accounts, we could take the old spreadsheets with notes, put them in Sheet2, and use a new macro to integrate old notes into an updated base file.

Basically: If a number in Sheet1, Column B, Row X matches a number in Sheet2, Column B, AnyRow, copy cells in Sheet2, Columns D through Z of the row where the matching number was found, and paste into Sheet1, Columns D through Z, Row X.

Does that help clarify at all?

[JieJenn]

Yeah, I guess. Can you post a dummy file? Rather do it once and do it right.

[grotz497]

It would look something like the attached book. Thanks for your help with all of this, we reall do appreciate it.

[JieJenn]

Wouldn't this macro work?

Please Login or Register  to view this content.

[grotz497]

For accounts not represented in Sheet1 that works perfectly. I was looking for something that would copy/paste specific cells based on accounts that are currently represented in both worksheets

[JieJenn]

My bad. I tend to not read the entire post when I am at work. Try this and let me know if this work.

Please Login or Register  to view this content.

[grotz497]

Works awesome! Last one... I think. How would I accomplish the following:

Check to see if an account number is in Sheets1 & 2 Column B, if it's not in Sheet2 Column B, change the text color of that account number to red Sheet1.

[JieJenn]

Give me the latest updated workbook ..... What's the address I need to send the invoice to?

[grotz497]

Here's the latest workbook! Thank you again, I'll see if I can expense your invoice...