Macro Button to add a row with existing formulas

[JCM]

What is the code to create a macro button that will insert a new row below that last row with all formatting of the previous rows

 

[JCM]

Oh great, I just realized I created an issue with all the following tabs that take the information from the first sheet.
Originally I created the first Master sheet with 30 rows. These 30 rows sent information to 10 other sheets (tabs) one for each year. Each of these 10 sheet also had 30 rows linked to the master sheet.

The intent of my request to add new rows to the master was to make it cleaner and not hinder entry is more than 30 rows were needed. For that to work when a row is added on the Master sheet it would also need to add the corresponding row to 10 tab worksheets.

I think I created a monster with my request

 

[Alex Blakenburg]

It is really hard to advise without knowing how the sheets are linked eg in which direction, row to row links or lookups or sum formulas
Do they all have the same layout ? Do they all have the formulas in the same places ?
How do we select the sheets, individual names or something in common in the names of sheets to be updated ?

You might want to try it manually to see what works.
Typically if the rows are linked you would need to "Group Select" the sheets and do the insert on all of them at once.

PS: In terms of keeping the formatting and formulas only if you use this where I had the commented out ClearContents line it may do what you need.
Rows(lRow).SpecialCells(xlCellTypeConstants, 23).ClearContents
The only issue would be, that if someone instead of entering say 50 into a cell, entered =15+35, it would be considered to be a formula and would not get cleared out.

 

[JCM]

I created the master sheet which has information for 10 years. the columns of information are the same for each year. The information entered there is calculated and sent to sheets for each year for additional information to track that year's growth. The worksheets are labeled CA YR 1, CA YR 2 through 10. I created the first one and copied it 9 times, so they are identical.

that is an interesting fact about the =15+35. There would be no reason to enter information like that.

 

[Alex Blakenburg]

Give this a try.
Pick / Change whichever set wsMstr line works for you.
It assumes you are going to act on that sheet (Master/Active) and every sheet with a name that starts with "CA YR"
It assumes that all sheets have the same number of lines.

Sub CopyRow_Log_MultiSheet()
    Dim lRow As Long
    Dim wsMstr As Worksheet, ws As Worksheet
   
    'Set wsMstr = Worksheets("Master")          ' <-- Change as required
    Set wsMstr = ActiveSheet                    ' <-- You can use this if your button is on the master sheet
   
    With wsMstr
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Select                                 ' Select first sheet to be grouped
    End With
   
    For Each ws In Worksheets
        If Left(ws.Name, 5) = "CA YR" Then      ' Assumes the target sheets start with CA YR
            ws.Select Replace:=False
        End If
    Next ws
   
    Rows(lRow - 1).Copy                     ' Copy row 1 row above the total row
    Rows(lRow - 1).Insert Shift:=xlDown     ' Insert above the last data row so the Total will include all data rows
    Rows(lRow).SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub