+ Reply to Thread
Results 1 to 13 of 13

Update Information on Sheet1 from Sheet2

  1. #1
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Update Information on Sheet1 from Sheet2

    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

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Update Information on Sheet1 from Sheet2

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    Quote Originally Posted by stnkynts View Post
    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

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Update Information on Sheet1 from Sheet2

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

  5. #5
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    Quote Originally Posted by stnkynts View Post
    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

  6. #6
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    Quote Originally Posted by stnkynts View Post
    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

  7. #7
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Question Re: Update Information on Sheet1 from Sheet2

    Quote Originally Posted by aviatecar View Post
    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

  8. #8
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    Quote Originally Posted by aviatecar View Post
    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

  9. #9
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Update Information on Sheet1 from Sheet2

    Good example. Try this:

    Please Login or Register  to view this content.
    Let me know if i missed anything.

  10. #10
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    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

  11. #11
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Update Information on Sheet1 from Sheet2

    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.

  12. #12
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Update Information on Sheet1 from Sheet2

    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.

  13. #13
    Forum Contributor
    Join Date
    06-29-2012
    Location
    Calgary CA
    MS-Off Ver
    Excel 2010
    Posts
    117

    Re: Update Information on Sheet1 from Sheet2

    Thank you so much Stnkynts!

    You have been so helpful!

    Best wishes,

    Chris

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  2. [SOLVED] update information on sheet1 from sheet2
    By aviatecar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-01-2013, 06:37 PM
  3. Update sheet2 with sheet1
    By tqm1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2007, 04:20 AM
  4. how to update data on sheet1 and have it auto update on sheet2
    By Tommy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-11-2005, 06:43 PM
  5. Copy some information from sheet1 to sheet2
    By john_liu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1