Excel Macro Insert Copied Row at top of Named Range - WITH formatting

Question

I want the end user to be able to click a green button on the right to insert a fresh row just under the header of the named range. The rows have dropdown selections and formulas so the new row will need all of those functions. The VBA code I have, that works for the "add material" macro is hard coded for a specific row (code below). I need the code to be dynamic because the insert location will adjust based on the ranges above.

Answer 1

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Answer 2

Assign the following macro to each of the command buttons:

Sub Button_Click()
    Dim r As Long
    r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row + 4
    Rows(r).Copy
    Rows(r).Insert
    Range("C" & r).ClearContents
    Range("K" & r).ClearContents
End Sub