View Full Version : VB Code To Update An Entry Based Upon # of Copies

06-16-2008, 09:14 PM
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"
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"
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

06-18-2008, 02:56 PM
Would really appreciate some help on this topic, thank you.