Macro that links cells from to sheets via formula after being inserted

[JohnMaier]

Hello,

I have a source data sheet (Let's call this sheet1). When cells on sheet1 are populated, cells on other sheets are automatically populated via formula (Let's call one of these sheets sheet2).
(i.e. Cell A1 on sheet2 contains the formula [='sheet1'!C2] to link cells A1 on sheet2 and C2 on sheet1; when C2 on sheet1 is populated, A1 on sheet2 is populated with the data from C2)

There is a section of sheet1 that often needs to be repeated several times within sheet1. I have added a macro to insert a blank section under the previous section within sheet1. This macro also inserts an additional section on sheet2.

I would like the additional sections to be linked once inserted. Is there a way to code this?

Neither of the sections exist until I run the macro to add the additional sections. Therefore I am unable to simply insert formulas to assign cells.

I will upload the actual workbook should you need to see my dilemma first hand. Please let me know.

Thank you for any assistance or suggestions you may have!

Regards,

John

[Mumps1]

It would help if we could see your file and macros.

[JohnMaier]

I have attached the Workbook.

[JohnMaier]

The actual sheets in question, are ACCT DATA and ACCT Summary.

On the ACCT DATA sheet you will see a button to "Add additional carrier". This will insert another carrier information section. It will also insert another section on the ACCT Summary tab. It is linking these two that has me stumped.

[Mumps1]

Hello John. I can now see your problem. I have to apologize that my level of expertise is not up to offering a solution. I did notice, however, that there is a pattern in the difference between the rows that contain the same data for each carrier in each sheet every time you add a new section. Perhaps a macro can be written where the formulas are copied using a counter to increment the row number following the patterns each time a new section is added. The columns are not a problem as they don't change. The expertise leve of members of this forum is incredible. I'm sure that one or more of them will be able to assist. Good luck with it and again my apologies.

[JohnMaier]

Thank you , Mumps1. It is greatly appreciated! I see your point regarding the pattern and counter and think this is a great idea. I had not thought of it from that direction. I will try to develope this and see if I can get it working. Thank you for your help!!

Regards,

John

[JohnMaier]

I was able to code the macro so that it finds the next empty cell in column A on the Acct Summary sheet, offsets to the cell which should contain the effective date of coverage, enters an equals sign in the cell, finds the next empty cell in column A on the Acct Data sheet, offsects to the cell containing the effective date, and selects the cell. However, it does not actually link the two. There is no formula created. It just puts an equals sign in the cell on the Acct Summary sheet.

Would anyone know how to code this so that part of linking the two cells includes finding the next cell, offsetting to find the correct cell, and linking them?

Thank you,

John

[JohnMaier]

Here is the updated file. I can get an equals sign in the correct cell, and select the correct cell on the Acct Data Sheet, but cannot link the two.

[kcarley]

Hi John!

Jeff was correct about the post you made elsewhere but I think you were hoping that Mumps1 might have done it for you. I understand what you're trying to do and since I prefer VBA I will try to avoid the potential solutions that come to mind with formulas. I'm sure there may be someone right along with that solution though... No need to bump this again, but since it has been 2 months... are you still stuck?

Regards,
K

[JohnMaier]

He solved my problem. I will mark complete.