Copy cells with formulas in macro

Question

I have a excel spreadsheet with 2 sheets. In the first sheet, I got some values, when I click on a button then in the second sheet the following happens 1. a new row is inserted at the top 2. the data from the first sheet is copied.

However, there are some columns with formulas after the copied cells, and I dont know how to do in vbscript to copy those formulas after your insert a new row.

Sub Trade1()
    Sheets("Trades").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2:R2").Select
    Selection.ClearContents
    Range("A2").Select
    Dim fromRange As Range, toRange As Range
    Set fromRange = Sheets("Enter Trade").Range("B2:B20")
    Set toRange = Sheets("Trades").Range("A2")
    fromRange.Copy
    toRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Sub

tigeravatar tigeravatar · Accepted Answer · 2013-08-20T19:02:16

After inserting a new row 2 (so that it's contents are now on row 3), I would copy row 3 back to row 2 (basically make a duplicate), and then override the values with the data you want to move from sheet 'Enter Trade' range B2:B20, like so:

Sub Trade_Button_Click()

    Dim btn As Button
    Dim wsEntry As Worksheet
    Dim wsTrades As Worksheet

    Set wsEntry = Sheets("Enter Trade")
    Set wsTrades = Sheets("Trades")
    Set btn = wsEntry.Buttons(Application.Caller)

    wsTrades.Rows(2).Insert
    wsTrades.Rows(3).Copy wsTrades.Rows(2)
    With Intersect(wsEntry.Range("A2", wsEntry.Cells(Rows.Count, "A").End(xlUp)).EntireRow, wsEntry.Columns(btn.TopLeftCell.Column))
        wsTrades.Range("A2").Resize(, .Rows.Count).Value = Application.Transpose(.Value)
    End With

    Set wsTrades = Nothing
    Set wsEntry = Nothing

End Sub

I have uploaded a modified version of your provided workbook here: https://docs.google.com/file/d/0Bz-nM5djZBWYa0R5T2hXMERabjg/edit?usp=sharing

In the modified version, I have mapped all of the Enter buttons to the above macro. When I click on a button, it copies over the correct column's values and also copies the formulas in the rest of the table within sheet 'Trades' successfully.

Copy cells with formulas in macro

1 Answers