Active X Button: Insert new row below with formulas and formatting

[sam987654]

Hello all,

I've done the work of inserting my own Active X command button (titled "New Entry"), and I was able to get it to work by applying the following code:

Private Sub CommandButton1_Click()
Rows(11).Insert
End Sub

This inserts a new row at Row 15, but it is a blank, default row. I'd like it to include all formulas and formatting that hidden Row 14 has. Here is a screenshot of my document

I've tried looking this up many times, but can't figure it out. Would anyone be willing to share answer?

 

[EXCEL MAX]

Hello Sam, welcome to the MrExcel forum.
Try with this code...

Private Sub CommandButton1_Click()
  
    Dim vC As Integer
  
    With ActiveSheet
        .Rows(15).Insert
        .Rows(14).EntireRow.Copy Rows(15)
        For vC = 1 To .Cells(14, Columns.Count).End(xlToLeft)
            If Not .Cells(15, vC).HasFormula Then _
                .Cells(15, vC).ClearContents
        Next
    End With

End Sub

 

[sam987654]

Hello Sam, welcome to the MrExcel forum.
Try with this code...

Private Sub CommandButton1_Click()
 
    Dim vC As Integer
 
    With ActiveSheet
        .Rows(15).Insert
        .Rows(14).EntireRow.Copy Rows(15)
        For vC = 1 To .Cells(14, Columns.Count).End(xlToLeft)
            If Not .Cells(15, vC).HasFormula Then _
                .Cells(15, vC).ClearContents
        Next
    End With

End Sub

Hi Max,

Thank you for responding. I see the new row inserting itself, but I see it behind this error message. Thoughts?

 

[sam987654]

Hi Max,

Thank you for responding. I see the new row inserting itself, but I see it behind this error message. Thoughts?

View attachment 36773

When I debug, the button becomes inactive

 

[EXCEL MAX]

Try to copy and paste my code, do not type.
My first thought is error in typing (X1TOLEFT) instead (XLTOLEFT).
Also try to create new button and paste this code.

 

[Fluff]

You're missing the .Column on this line

        For vC = 1 To .Cells(14, Columns.Count).End(xlToLeft).Column

 

[EXCEL MAX]

Yes, I forgot that.
I have some numbers in the column "K" and it couse code in my sheet works properly.
Thanks for the correction.

 

[Fluff]

You could also get rid of the loop like

    With ActiveSheet
        .Rows(15).Insert
        .Rows(14).EntireRow.Copy .Rows(15)
       .Rows(15).SpecialCells(xlConstants).ClearContents
    End With

 

[sam987654]

You're missing the .Column on this line

        For vC = 1 To .Cells(14, Columns.Count).End(xlToLeft).Column

You're missing the .Column on this line

        For vC = 1 To .Cells(14, Columns.Count).End(xlToLeft).Column

Welp! It looks like this worked. Thank you both so much. The only remaining thing.... I want to copy Row 14 but also keep it hidden. What's happening is that any new rows created are also staying hidden. I'm having to unhide Row 14 for the others to show

You could also get rid of the loop like

    With ActiveSheet
        .Rows(15).Insert
        .Rows(14).EntireRow.Copy .Rows(15)
       .Rows(15).SpecialCells(xlConstants).ClearContents
    End With

You could also get rid of the loop like

    With ActiveSheet
        .Rows(15).Insert
        .Rows(14).EntireRow.Copy .Rows(15)
       .Rows(15).SpecialCells(xlConstants).ClearContents
    End With

 

[sam987654]

Yes, I forgot that.
I have some numbers in the column "K" and it couse code in my sheet works properly.
Thanks for the correction.

This was genius. I added in .Column, and it's coming together