Macro to insert row at the end of sheet

[Nuzza]

I've browsed through these forums and although there's alot of topics
discussing how to add rows using macros, I can't seem to find a specific
answer to my question.

I have a spreadsheet which contains data (formulae, drop-down fields and
general user-entry fields). I want to insert a button on the last row of the
worksheet which contains a macro that inserts a row just above this button
(on the second last row). I want the formulae, formatting and drop-down
fields to be copied when the new row is inserted. I don't want any prompts as
to how many rows are inserted, just simply click the button to insert 1 row
at a time.

Any help please?

 

[Ron de Bruin]

Hi Nuzza

You can try this macro with a button from the forms toolbar on your sheet

I changed David's macro a bit
See link to page from David McRitchie in the code

Sub InsertRowsAndFillFormulas_caller()
'-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]>
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
Dim x As Long
Dim rw As Long

rw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row - 1
Rows(rw).Select 'So you do not have to preselect entire row
vRows = 1

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

 

[Nuzza]

Hi Ron,

Thanks for your response.

I've added the code into the Visual Basic Editor, but when I run the macro I
get an error message saying "Type mismatch". What am I doing wrong?

 

[Ron de Bruin]

Do you copy the code in a normal module

Alt F11
Insert Module

Do you use a button from the forms toolbar to run the code ?

 

[Nuzza]

I don't know much about VB and what the modules are for. I open the editor
then see all the sheets contained within my spreadsheet, and paste the code
in the section "ThisWorkbook".

Ron, are you saying I should insert a module then paste the code in there
instead?

 

[Ron de Bruin]

Ron, are you saying I should insert a module then paste the code in there

instead?

Yes

See
http://www.cpearson.com/excel/codemods.htm

 

[Nuzza]

This line of code seems to be causing problems:
rw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row - 1

When I run the macro and get the "Run-time error 13: Type mismatch" error
then click on Debug, that line of code is highlighted in yellow.

 

[Ron de Bruin]

Do you use a button from the Forms Toolbar ?
It is not working if you usa a control toolbox button