Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jun 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Code To Update An Entry Based Upon # of Copies

    I am currently working in Excel and have some background writing code. However I am really stuck with this one. I have 2 worksheets that are going to be the main source of my discussion. One is entitled investors, and the other CCs. In the "Investors" worksheet I have entries detailing a summary of information about each investor's gains/losses etc. Each investor however can and usually will require more than one copy of the report that is generated through this table. Once this table is complete (I have manually been updating this myself), I use Microsoft Word's "Mail Merge" tool to import the data from my excel spreadsheet (a specific range) and insert it into a pre-formatted template in Word, so that all I need to do is print out my reports.

    Example of "Investments"
    A B C D E
    1 Client Gain/Loss Total Committment ETC.
    2 xx 344 4324332 433 423423
    3 yy 223 3423423 324 665655
    4 zz 232 7666566 766 765756

    Example of "CCs"
    A B
    1 Client #Copies
    2 xx 1
    3 yy 1
    4 zz 1

    I need help writing a formula that will enable me to update "Investments" when I change the number of copies each client wants. If I change the #of copies for "xx" to 3, I need the entire row A2 to be copied and 2 copies to be inserted on the next 2 rows (where company "xx" & "yy" are; without deleting those entries, but just shifting those cells down [Rows A3 &A4]). However I also need the formula to delete an entry if I change the number of copies from, i.e. 3 to 2. The way my worksheet is formatted, the original row for each client is highlighted in blue. Each additional copy that is created should not have any background fill color, and when a delete update needs to occur, the copies get deleted and never the original. There must be a statement in the code specifying never to delete the original. (The number of copies column can never have an entry that is less than 1.

    If someone can help me with this it would be much appreciated.

    *****Lastly I have an investment sheet for each quarter and would like this process to be automated as much as possible so if it can be done, the # of copies needs to update 4 sheets "Investors 1/1-3/31", "Investors 4/1-6/30", "Investors 7/1-9/30", "Investors 10/1-12/31"********

    -Thank You

  • #2
    New to the CF scene
    Join Date
    Jun 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Would really appreciate some help on this topic, thank you.


  •  

    Posting Permissions

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