Adding rows and not messing up VBA code

[suzette0735]

Hello, I am working on creating an excel form and would like it to have the option to add new sections if the user needs additional space. I'll insert a screen shot.
I would like them to be able to some how, maybe click on a button that is Titled "Additional Sections" and an additional section would be added each time they select this.....I guess i would need two buttons as another would add rows in another section and this would be titled, "Additional Alternate Sections". It doesn't have to be a button, just trying to think of an easy way for them to see and have user select.
Is this even possible? How would the macros i already have in place be able to update taking into account the additional row additions not knowing how many they will need?

The first screen shot gives an example of the sections (not all columns are shown, there are formulas and dropdowns in some cells) I would want the rows from select category-description to be added again and again if needed.

The second screen shot gives an example of the alternate sections (not all columns are shown, there are formulas and dropdowns in some cells) I would want the rows from select category-description to be added again and again if needed except here above the deduct section.
Thanks for your help in advance!!

 

[sxhall]

Hi suzette0735,

From reading your post there are two (maybe three) parts to this...

• you want to be able to add in additional sections like the below when user clicks on a button (this is the best way to activate it to do this)? And possibly add in another section on the second tab?
• you require another sheet to update formulas to capture the added sections?

Section to add in each time?

Both can be done with the following...

For the first part recording the actions of copying an existing section and then adding it in would work. Using the code below to determine the row number where to paste/insert the copied section to.​

For the second part using the below code to determine the end row for the formulas and then substitute it in where required.​

The below adds up the amount in column C to cell A1 using LRow for the formula.

Sub Macro1()

Dim LRow As Integer

'This determines the last row (LRow) being used, this will be substituted into formulas...
LRow = Range("C1").End(xlDown).Row

'Change 'FormulaR1C1' to plain 'Formula' and you can then use use the cell
'references as you would see in the cell.  This allows us to use 'LRow'...
Range("A1").Formula = "=SUM(C1:C" & LRow & ")"

End Sub

This is how the formula would look in a recorded macro...

Sub Macro2()

'Recorded with 'FormulaR1C1'....
Range("A1").FormulaR1C1 = "=SUM(RC[2]:R[8]C[2])"

'Alternatively we can just reference the whole column if possible...
Range("A1").Formula = "=SUM(C:C)"

End Sub

Let me know if the above is of help or if you need more help, let me know.

Sxhall