Macro to link cells from two sheets after they are inserted

Hello JohnMaier
In the attached I have given an in-principle example of how to add your linked "formulae".
In-principle because I have effectively hard coded the linked cell addresses to prove the principle.
I have added links in Col C only in the 'ACCT SUMMARY' sheet.
I will leave it for you, to work out and code the the relative cell offsets/addresses as you add further carriers.
Add a carrier and populate the fields: Effective, Coverage Type, Limit, TIV and Rate to see them appear in the summary.
The example code is at the bottom of module 'Additionalcarrier'.
Hopefully, you may be able to make use of this to take your project forward.
Barry

Account_Workbook amends.xlsm

Would there be a way to hard code them as you suggested, but then offset the cells that are linked when I add another carrier so that it links the correct cells?

Example: If I had the following to link the cells when the first additional carrier was added...

Please Login or Register  to view this content.

Can I offset the "R[55]" portion automatically when adding the next section?

Thanks.

Hello John
Absolutely.
Essentially my example illustrated the syntax of how to link cells within VBA - your original frustration.
I left the placement of the references in ACCT SUMMARY to you, based on the dynamics of your added data lines and knowledge of your coding.
The "hard coding" of the references in my example code was purely for expedience and not a suggestion - my poor explanation.
You are on the right track now - placing the references dynamically as you add new carriers/data lines.
I think you you will be able to complete this part of your project now.
Happy to keep in touch on this if it helps.
Well done.
Barry

Thank you for all of your help Barry! However, I am still confused as to how the cells would automatically link when I insert a third or fourth carrier. Please see my concept below. I have tried to further explain what it is I am attempting to do.



[CONCEPT]:

1. Each time an additional carrier is added to the ACCT DATA sheet, the macro copies a blank POST BINDING Section from the far right of the sheet. It then inserts that section below the existing POST BINDING Section. It does this by finding the next empty cell in column A, and then inserting the section at that row (shifting the remainder of the sheet (the notes and instructions ) down).

2. The copied section itself, contains an * in each row which are pasted into column A. This helps the macro find the end of each additional section and insert a new one below it.

3. This takes place on the ACCT SUMMARY Sheet as well. Inserting a new section under the existing section.

[U][B][CODE1]Please Login or Register to view this content.[/code]

[U][B][CODE2]Please Login or Register to view this content.[/code]


The $1MM QUESTION: Is it possible to use CODE1 to find a desired cell on the ACCT SUMMARY sheet, select it, and insert a formula that includes CODE2 and essentially, finds & links to a desired cell on the ACCT DATA Sheet?

EXAMPLE:

The example code below would find the desired cell on the ACCT SUMMARY sheet and insert a formula in that cell. This formula would find the desired cell on the ACCT DATA sheet and select it. Making the desired cell on the ACCT SUMMARY sheet equal to the desired cell on the ACCT DATA sheet.

Please Login or Register  to view this content.

In formula form, it may look like this: [Selected cell obtain by Code1 = Selected cell obtained by Code2]



I am basically trying to link two cells that do not exist yet! HAHA!
Am I crazy, or is there a way to do this?

Thank you for all of your time. I know this is a tough one.

Regards,

John

Hello John
I will take a look at this today for you.
Would you take a few minutes to read the forum rules and how to use code tags to enclose code examples.
Perhaps you could edit your last post to this effect?
Appreciate the explanation, references to your uploaded sheet are OK rather than reproducing code in posts.
Will get back to you.
barry

Hello John
Thanks for attending to code tags etc.
This is such a fantastic forum that we need to ensure our standards are maintained for all our benefits.

OK, I have attached a version which I think does the job, ready for your testing.
I have also added 'Rate' formula in your ACCT SUMMARY template cell XEV15, missed in error I suspect.
Also corrected a couple of typos in the ACCT DATA sheet cells D4 and D12.

Let me know if it meets your needs.
barry

Account_Workbook JohnMaier amends v2.xlsm

Thank you John
Glad to be of help.
Please mark this thread as SOLVED.
Barry