Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » VBA » Link two seperate worksheets in Excel 2003 and not allowing blank cells

Link two seperate worksheets in Excel 2003 and not allowing blank cells

Previous | Next
Latest post Fri, Mar 14 2008 4:45 PM by Anonymous. 2 replies.

  • Wed, Mar 5 2008 11:48 AM

    Link two seperate worksheets in Excel 2003 and not allowing blank cells

    Reply |Contact
    I have a question that I am not sure is VBA related…but here it goes.I have one Excel document that has different users enter specific information about a product.The completed document is then emailed and then someone copies the data and pastes into another master Excel document.  My questions is: what is the code that automatically takes the data entered into the document and automatically populates the master document and places the data at the very bottom of the master document?  Any ideas where I could start finding more information about this, or getting code for this?ANDI also need code that recognizes blank cells and has a warning box appear if there is a blank cell.  All cells needs to have some type of information and cant be sent without information in there.I used the tutorial from Mike's website that has a macro where an email is automatically sent by clicking the button.  Is there additional code that can be added to the macro that checks for blank boxes?  Any information would be great!  Thank you!!!  jphess@perrigo.com

     
    Filed under: , ,
    • Post Points: 21

  • Wed, Mar 5 2008 12:45 PM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 74
    • Points 1,231
    • MVP

    Re: Link two seperate worksheets in Excel 2003 and not allowing blank cells

    Reply |Contact |Mark as Answer

    Ron de Bruin has lots of code on his site that will deal with merging data from different sheets, different workbooks etc.
    try starting here
    http://www.rondebruin.nl/copy2.htm
    You will also find code for sending files by email.

    As far as checking whether all columns have been filled on each row, you could have a a hidden column with a checksum in it.
    For example, if you were expecting users to complete columns A through M, then in column N have
    =IF(COUNTA(A2:M2)<13,"Incomplete","Complete")

    Then use Conditional formatting for the the whole data area A1:Mxxx with a Formula of =$N1="Incomplete" and set the format to Red
    Each row will remain Red until the user has completed all of their entries

    Regards
    Roger Govier
    Microsoft Excel MVP
    • Post Points: 21

  • Fri, Mar 14 2008 4:45 PM In reply to

    Re: Link two seperate worksheets in Excel 2003 and not allowing blank cells

    Reply |Contact |Mark as Answer

    The formula you listed worked great, thank you!  I added the validation and it is very good for this document.

    Thanks for the link to code, it is exactly what I was looking for.
    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.