Copying formula and Format from row above Macro

Question

I have a spreadsheet for entering a new set of data on a new row each day each day, the row contains formulas and formatting. I want to be able to click a button and it adds a row under the last row with entered data and copy the formulas and formatting only, ready for new data to be entered.

Below is my code:

Sub Button1_Click()
    Dim ws As Worksheet
    Dim varUserInput As Variant

    Set ws = ThisWorkbook.Sheets("Summary")

    With ws
         varUserInput = .Range("D" & .Rows.Count).End(xlUp).Row

        .Rows(varUserInput).Insert Shift:=xlDown

        .Rows(1).Copy .Rows(varUserInput)

        .Rows(varUserInput - 1).Copy
        .Rows(varUserInput + 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
End Sub

The issue is that it will only copy the formula from the same hidden row each time.

Which row has the formatting? – Siddharth Rout Sep 07 '15 at 11:02 — Siddharth Rout, Sep 07 '15 at 11:02
any row above this new row, but row 3 at the moment – J.Goddard Sep 07 '15 at 11:07 — J.Goddard, Sep 07 '15 at 11:07
Try the code that I posted.. Is that what you want? – Siddharth Rout Sep 07 '15 at 11:11 — Siddharth Rout, Sep 07 '15 at 11:11
yes worked perfectly! thank you – J.Goddard Sep 07 '15 at 11:39 — J.Goddard, Sep 07 '15 at 11:39

score 1 · Accepted Answer · answered Sep 07 '15 at 11:09

1

Is this what you are trying (UNTESTED)?

Sub Button1_Click()
    Dim ws As Worksheet
    Dim varUserInput As Variant

    varUserInput = InputBox("Enter Row Number where you want to add a row:", "What Row?")

    If varUserInput = "" Then Exit Sub

    Set ws = ThisWorkbook.Sheets("Sheet1") '<~~ Change as applicable

    With ws
        .Rows(varUserInput).Insert Shift:=xlDown

        .Rows(1).Copy .Rows(varUserInput)

        .Rows(varUserInput - 1).Copy
        .Rows(varUserInput).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
End Sub

answered Sep 07 '15 at 11:09

Siddharth Rout

147,039
17
206
250

that is perfect, worked just as I wanted! Thank you very much – J.Goddard Sep 07 '15 at 11:14
would there be a way of adding a row below the last in the list rather than selecting the row number? – J.Goddard Sep 07 '15 at 11:42
Yes. Please see [THIS](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) to find the last row which has data. Simply add 1 to that row to get the new row – Siddharth Rout Sep 07 '15 at 11:43
Brilliant, very helpful – J.Goddard Sep 07 '15 at 11:50
Update your question with the code that you are trying now – Siddharth Rout Sep 07 '15 at 12:00
I'm using exactly what you posted and nothing else – J.Goddard Sep 07 '15 at 12:36
I haven't got it in, I cant make it work unfortunately. Instead of asking what row I want to add, i'd like it to automatically find it using that line but I can work out what to remove and what to add – J.Goddard Sep 07 '15 at 12:50
I can give you the code but then you will not learn.. Please read the link that I gave you on how to find the last row... – Siddharth Rout Sep 07 '15 at 12:51
thats fair enough, I shall update the question with the closest I have come – J.Goddard Sep 07 '15 at 12:54

Copying formula and Format from row above Macro

1 Answers1