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.

 

[JCM]

Good morning Alex,
It did work perfectly when I connected it to my command button. What I did not notice was that when it added the new row it copied the entire row including the calculated results for information that is entered in designated sells. Let me explain. 1st row cell A, a name is entered, 2nd cell date of entry, 3rd units are owned, 4th units to be sold, 5th cell is where the difference is calculated, the 6th cell the market price, the 7th cell calculates the value of 5 and 6. The following repeats through column AZ when there are cells that information is entered and cells that calculate the information entered.

To clarify, of correct my previous request, I need to add a new row, with the macro button, that only includes the formatting when it creates a new row, whereas now it creates a copy of the row above that already has been filled out cells of information that is not relevant to a new row. I sure hope I explained that correctly. So if correct what I need is to insert a new row at the bottom but above the columns that total the information and is visible in the bottom row, with formatting (some cells are colored) and formulas (some cells calculate previous blank cells once information is entered)