Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    Could you attach a zip file with stripped down copies of the workbook(s)?

  2. #2
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Merge HELP! (2007)

    <P ID="edit" class=small>(Edited by kristi626 on 25-Feb-08 22:27. Added file example)</P>How do you merge data from different worksheets? Here is my problem.... I have created a 3 month sales forecast spreadsheet that needs to be updated every month. How can I merge data from my old forecast into a new forecast without losing data from my old forecast?
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    On which column or columns should the Old and New sheets be matched?

  4. #4
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    All of them. prior billing on OLD should be updated by sheet NEW... There may be rows on sheet NEW that aren't on sheet OLD that needs to show (without forecast). Does that make sense?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    If we have to match on ALL columns it wouldn't be possible to update rows - if Prior Billing is different between the two sheets, the rows don't match...

    Added later I realize that I probably wasn't clear enough. What I wanted to know is which column or columns should be used to determine whether a row in the New sheet belongs to a row in the Old sheet. Project # alone clearly isn't sufficient, perhaps Project # and Phase ID?

  6. #6
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Gotcha...... The columns to determine which rows match the OLD and NEW sheets are: Project #, BG ID, and Phase ID. I want to be able to have the OLD sheets PRIOR BILLING column updated with the NEW sheets PRIOR BILLING, thus updating the Fee Balance, but keeping the OLD forecasts for each month February, March, April.

    Does that make sense?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    I have attached a text file with a macro that you can use - test it on a copy of your workbook! I hope that it does what you want.
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Forgive me, but I am an admitted MACRO idiot. What exactly do I do to run this once I open my sheet. Thanks in advance for your patience.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    I don't have Excel 2007, so I have no idea how macros work there. Someone else will have to help you with that.

    I have attached a version of your workbook in Excel 97-2003 format (zipped) with a command button that can be clicked to run the macro.
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    I'll work with this and let you know how it does. Thank you so much for your help!

  11. #11
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    I tested this and it works great! You ROCK! You don't know how much time this saved me!!!!!!!!!!!

  12. #12
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    I have been working with this most of the day and have found only one issue with using this for my forecast template. Sometimes the contract fees change (the column labled "Contract "Fee") because, for example, the Client agrees to a fee increase. Is there a way to modify this macro so that this column gets updated with the others?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    Start by making a backup copy of the workbook, just in case.

    Press Alt+F11 while in the workbook to activate the Visual Basic Editor.
    Search for the line
    <code>
    wshOld.Range("H" & s) = wshNew.Range("H" & r)
    </code>
    Click at the end of this line, then press Enter.
    Type or copy/paste the following in the blank line that appears:
    <code>
    wshOld.Range("G" & s) = wshNew.Range("G" & r)
    </code>
    Column G is the Contract Fee column.
    Press Alt+F4 to leave the Visual Basic Editor, then save the workbook.

  14. #14
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    It worked! Thanks again, Hans!

  15. #15
    New Lounger
    Join Date
    Jan 2007
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Is this macro specific to a certain number of rows of data? I ask because they may differ among our 5 different departments.

Page 1 of 2 12 LastLast

Posting Permissions

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