asked on

Automatically adding a line in an Excel Worksheet

I want users to be able to add a line in the attached worksheet. Currently, there is a crude macro (Ctrl Shift L) that adds the line. In order for the macro to work, you must be on the first blank like in Column A.

I want the macro to automatically find the first cell below B15 that is blank (in the of the attached worksheet, B46) and automatically copy C45 to C46, F45 to F46, G45 to G46, H45 to H46, I45 to I46 and K45 to K46 and the cursor ends up, once again in the case of the attached worksheet, in that first blank cell in Column B, B46.
NOTE-PAYABLE--BB--Inc.-to-AA--LLC-R.xlsm

Roy Cox

If the data was laid out correctly, without merged cells and empty columns then using a Table would make VBA unnecessary.

Overview of Excel Tables

You do not need to make the sheet visible.

If you use Application.ScreenUpdating = False, then you must restore it, although you don't really need it.

Change the sheet name as required

Sub NEWLINE()
'
' NEWLINE Macro
' INSERT NEW LINE
'
' Keyboard Shortcut: Ctrl+Shift+N
    Dim NextRw As Long

    With Sheets("GSK-NP-GMTH 2015")
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        Sheets("Formulas").Rows("4:4").Copy
        .Cells(NextRw, 1).Insert Shift:=xlDown
    End With
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Golden

ASKER

That worked perfectly. The Year-End calculation is my next question to post. It is actually a routine to compound interest.

Roy, I obviously did a bad job of explaining what I was looking for. I did run your routine about 30 times trying to get it to not error out, but I was unsuccessful. Thanks for trying though.

Roy Cox

The only error would be if you didn't change the Sheet namr, it worked perfectly for me whereas the code you have chosen only works on the activesheet, if you had used ActiveSheet with my code it would have run fine, as you asked for and more efficiently!

Also, as I said VBA is totally unnecessary if you take the time to read the page that I suggested.
NOTE-PAYABLE--BB--Inc.-to-AA--LLC-R.xlsm