Copying above row formatting and formula to new row using command button

[JoRyBar]

Hello VBA Gurus,

I would like to create a new row with a command button and then have VBA codes copy the formatting and certain formulas from the row above into the newly inserted row. I would like to only copy formulas from certain column ranges (i.e. A:B; D:L, U:AA) and leave the rest blank. The code below only inserts the new row, but does not copy the formatting or formulas from the above row:

Private Sub CommandButton1_Click()
Dim rowNum As Integer
On Error Resume Next
rowNum = Application.InputBox(Prompt:="Enter Row Number to Add a Row Above:", _
Title:="Add New Row", Type:=1)
Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown
R = Rows(rowNum & ":" & rowNum).Row
Range("a" & R + 1 & ":b" & R).Formula = Range("a" & R + 1 & ":b" & R).Formula
Range("d" & R + 1 & ":l" & R).Formula = Range("d" & R + 1 & ":l" & R).Formula
Range("u" & R + 1 & ":Aa" & R).Formula = Range("u" & R + 1 & ":Aa" & R).Formula


End Sub

Your assistance is greatly appreciated! Many thanks.

Best Regards,

VBA Novice

 

[Yongle]

Welcome to the forum
Please enclose code tags around your code (click on # icon above post window and paste code inside the tags that appear)

Try this

Private Sub CommandButton1_Click()
    Dim rowNum As Long
    On Error Resume Next
    rowNum = Application.InputBox(Prompt:="Enter Row Number to Add a Row Above:", _
        Title:="Add New Row", Type:=1)
    Rows(rowNum).Insert Shift:=xlDown
    If Err.Number > 0 Then GoTo errH
    Range("A" & rowNum - 1).Resize(, 2).Copy Range("A" & rowNum)
    Range("D" & rowNum - 1).Resize(, 9).Copy Range("D" & rowNum)
    Range("U" & rowNum - 1).Resize(, 7).Copy Range("U" & rowNum)
errH:
End Sub

 

[JoRyBar]

Thank you, Yongle! It worked perfectly