Thread: Copying a row and pasting the same below it with just formulas

Hello!

First of all, this really is an amazing resource!!

Now, to my query. Any help will be much appreciated. I have attached the file. The row being copied is row "X" for both buttons

I have 2 buttons in the same worksheet to copy a row and insert the
copied row below it. I have this macro running for 2 different rows in the worksheet, assigned to the respective 2 buttons. See my code below.

______________________________________________________

Private Sub CommandButton1_Click()
    Range("$B$7:$L$7").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton2_Click()
    Range("$B$12:$L$12").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

________________________________________________________

The problem is:

1) I want just the formulas/formatting to be pasted, and not the
entered values.
2) And more importantly, if I use Button 1, then row 7 is copied and the copied row is added below row 7 and the rows move down. But then, for Button 2, row 12 is different....now as it has moved down (it has become 13, but the macro for button 2 is still copying row 12), if you understand? How do I keep it fixed that it always copies the contents of row 12 even if a row is added above and the rows move down, making row 12 into 13 and so on.

Thanks again,

Shivam

Hi Shivams, welcome to the forum. This forum is a great resource, but certain rules are strictly enforced, rules you agreed to just now when you joined.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used.

The tags are demonstrated in my signature, your VBA code goes between them.

Fix that post above, and consider using the PAPERCLIP icon (GO ADVANCED) to upload your workbook so we can look and see if we can spot another way do what you want. Highlight any pertinent cells in the workbook to draw our attention.

See, once we look at the sheet we can spot a "fixed feature" of your worksheet so we just find that "fixed position", then copy the row which is always a fixed distance away.

Private Sub CommandButton1_Click()
Dim rFind As Range
Set rFind = Cells.Find(What:="TRADED AWAY", After:=[A1], LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    With Range("B" & rFind.Row + 2 & ":L" & rFind.Row + 2)
        .Copy
        .Insert Shift:=xlDown
        Application.CutCopyMode = False
    End With
Set rFind = Nothing
End Sub

Private Sub CommandButton2_Click()
Dim rFind As Range
Set rFind = Cells.Find(What:="RECEIVED IN TRADE", After:=[A1], LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
    With Range("B" & rFind.Row + 2 & ":L" & rFind.Row + 2)
        .Copy
        .Insert Shift:=xlDown
        Application.CutCopyMode = False
    End With
Set rFind = Nothing
End Sub