Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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 ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Nuzza" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Nuzza" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert row at the end of sheet
Do you use a button from the Forms Toolbar ?
It is not working if you usa a control toolbox button -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Nuzza" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a macro to insert a macro | Excel Discussion (Misc queries) | |||
Can not insert sheet??? | Excel Worksheet Functions | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) | |||
insert query into excell sheet to update excell sheet and pivot table | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |