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" wrote:
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
' Insert Rows -- 1997/09/24 Mark Hill
' 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).SelectedShee ts
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
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Nuzza" wrote in message ...
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?