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

 

[mumps]

Try:

Sub CopyRow()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Rows(lRow).Copy Range("A" & lRow + 1)
End Sub

 

[JCM]

Sorry I am having problems getting the button correctly. I will fix that process first and let you know. Until then Thanks for taking the time, it is greatly appreciated.

 

[mumps]

You would have to create the button first and then assign the macro to it.

 

[JCM]

I set up the button and it did not work, or I thought it did not work then I realized at the bottom of the sheet was a total column and your code kept inserting a row below the total column. lol
So if the last row is 15 and the TOTAL ROW (so labeled in column A row 16) is row 16 how do I get it to insert a new row above the total row with all the formating in row 15?

 

[Logit]

Sub CopyRow()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Rows(lRow).Copy
    Rows(lRow).Insert Shift:=xlDown
End Sub

 

[Alex Blakenburg]

Is it only formatting that needs to come down from the previous row ?
Does the new row need to blank out constants but bring down formulas ?
Wouldn't you better off using an Excel Table ?


@Logit is 1 row out. lRow is the total row.

Sub CopyRow_Log()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    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).ClearContents               ' <--- Optional - if you want the last row to be blank
End Sub

 

[JCM]

Alex that worked perfectly. Thank you very much. I will study it to try to understand the logic.
Again Thanks
JCM

 

[Alex Blakenburg]

Let us know if you need help with the logic.