insert new row and copies formula from columns L:P

[miming1986]

I'm relatively inexperienced at VBA code and was hoping someone could help.
The code below copies data from range B2:AC2 from the "Release" sheet and inserts a new row and pastes the data to the "Record" sheet. Problem is I now want to exclude L2: P2 so when it pastes to the new row inserted, it just automatically fills the formula above them. Thank you so much in advance!

Sub Generate()
Dim RowNum As Long
If MsgBox("Release Loan and Record?", vbYesNo) = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    Sheets("Release").Range("B2:AC2").Copy
    Sheets("Record").Activate
    RowNum = 2
    Do While Not Len(Cells(RowNum, 1).Value) = 0
        RowNum = RowNum + 1
    Loop
    Cells(RowNum, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
    Application.ScreenUpdating = True
End Sub

 

[Ajesh]

Hi miming1986,

What do you mean by excluding L2: P2? I don't see any exclusion in your code.

Where are you pasting? I.e. Is it a normal range or end of List Object (Table)?

Can you upload a sample file pls?

Thanks/Ajesh

 

[miming1986]

Hello Ajesh!

Yes there are no exclusions on that code because I don't know how to do that in VBA. I'm pasting to a normal range (not a table). I have uploaded the file for reference.

 

[miming1986]

I am thinking of adding this code. How do I loop (copied formula to "adjust") so when a new row is inserted, the formulas above them get copied?

Range("L2:P2").Copy Range("L3:P3")

 

[Ajesh]

Try this: -

Sub Generate()
Dim RowNum As Long
If MsgBox("Release Loan and Record?", vbYesNo) = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    Sheets("Release").Range("B2:AC2").Copy
    Sheets("Record").Activate
    RowNum = 2
    Do While Not Len(Cells(RowNum, 1).Value) = 0
        RowNum = RowNum + 1
    Loop
    Cells(RowNum, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With ActiveSheet
        .Range("L" & RowNum & ":P" & RowNum).FillDown
    End With
    Application.ScreenUpdating = True
End Sub

 

[miming1986]

That is exactly what I am looking for!

Thank you so much Ajesh!