Update Information on Sheet1 from Sheet2

[aviatecar]

Hello Gurus:

I need a VBA code to help me update information on sheet1 from sheet2.
I have my sample file attached and a Pseudo code written.

Thanks and have a great day!

Chris

Update Information on Sheet1 from Sheet2.xlsx

Pseudo Code:
1, For the first item in column A (cell A1) on sheet2, search a match from the whole column A in sheet1

2a, If a match is found at cell Ai on sheet1, copy the cell E1 from sheet2 to cell Ei on sheet1.
Continue until all of the matches of cell A1 on sheet2 are found from column A on sheet1.

2b, If no match can be found, add row1 (or cell A1~E1) of sheet2 to the bottem of sheet1

3, Repeat the above steps for cell A2, A3, A4 etc....etc... of sheet2

[stnkynts]

Please Login or Register  to view this content.

[aviatecar]

Please Login or Register  to view this content.

Dear Stnkynts:

Thank you for the code! It works like a charm!

However, I am wondering if you can modify the code so that it add values from E column on sheet 2 to the NEXT AVAILABLE COLUMN on sheet 1.
i.e. If there are values in column D, E, F, on sheet 1, then the column E on sheet 2 should be added to column G on sheet 1 etc.etc...

Thank you again and I am looking forward to your response.

Chris

[stnkynts]

I am afraid I don't understand. Maybe a new "How I want Sheet1 to be" example would illustrate it better.

[aviatecar]

I am afraid I don't understand. Maybe a new "How I want Sheet1 to be" example would illustrate it better.

Dear Stnkynts:

I am sorry but I don't have a computer around so I cannot provide a how I want it to be sheet right now. But I can explain.

Before we ran the first code, there were 4 columns on sheet1, and after we ran it, there were 5 column because a column E was added to sheet 1. What I need to do now is update sheet 1 from a NEW sheet 2, however, column E on sheet 1 is already occupied so column E on sheet 2 needs to go column F on sheet 1. I need to do this for many new sheet2s so I want column E on sheet 2 go to the next available column on sheet 1.

Thanks you and please let me know if you need further clarifications.

Chris

[aviatecar]

I am afraid I don't understand. Maybe a new "How I want Sheet1 to be" example would illustrate it better.

Ok hold on, I am working on a "How I want it to be" sheet. The modification is a little different than my original description.

Thanks,

Chris

[aviatecar]

Ok hold on, I am working on a "How I want it to be" sheet. The modification is a little different than my original description.

Thanks,

Chris

Ok attached is the new "How I want it to be" workbook and a new pseudo code.

I hope this helps.

Thank you and I am looking forward to your response,

Chris

Update Information on Sheet1 From All Other Sheets Within the Same Workbook.xlsm

[aviatecar]

Ok attached is the new "How I want it to be" workbook and a new pseudo code.

I hope this helps.

Thank you and I am looking forward to your response,

Chris

Attachment 343522

Hi Stnkynts:

I realized that this is quite different than the original one. Do you want me to open a new thread?

Please let me know,

Chris

[stnkynts]

Good example. Try this:

Please Login or Register  to view this content.

Let me know if i missed anything.

[aviatecar]

Dear Stnkynts:

Again it works like a charm!

Thank you so much for your help!

I have a question here:

If wksht.Name <> ws1.Name And wksht.Name <> "How I want Sheet1 to be" Then 'you can remove the And wksht.Name <> "How.....from your real sheet

In real situation, there is no sheet named "How I want it to be". So can you remove this condition for me? I tried but it wouldn't compile....

Thank you,

Chris

[stnkynts]

The new line would be (replacing old line):

Please Login or Register  to view this content.

This line needs to be in the code or else you are going to have issues.

[stnkynts]

The new line would be (replacing old line):

Please Login or Register  to view this content.

This line needs to be in the code or else you are going to have issues.

[aviatecar]

Thank you so much Stnkynts!

You have been so helpful!

Best wishes,

Chris