|
Insert Rows Macro
Hi, I am trying to get a macro to insert rows and fill
formulas. I want to hit a button, have a message box open which asks "How Many Rows To Insert" and then begin inserting and filling formulas. Unfortunatly my eyes are bigger than my VBA skill. I have this macro which I have adapted before (thanks to this group). I only want to do the insert on the active worksheet. How do I fix it? Thanks so much, Todd Sub InsertRowsAndFillFormulas(Optional vRows As Long) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'type 1 is number If vRows = False Then Exit Sub End If '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 Integer 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 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 |
Insert Rows Macro
Hi Todd,
For a generic solution see Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm Select the row that is to be copied down, The entire row gets copied down and constants are removed, no need to change the subroutine to identify which columns have formulas. You will have to code balances or rows with cells that refer to other rows to use OFFSET all described on the web page. If not familiar with macros see the top of the page, you might also look at the toolbars.htm page for your toolbar icon and it's installation. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Todd" wrote in message ... Hi, I am trying to get a macro to insert rows and fill formulas. I want to hit a button, have a message box open which asks "How Many Rows To Insert" and then begin inserting and filling formulas. Unfortunatly my eyes are bigger than my VBA skill. I have this macro which I have adapted before (thanks to this group). I only want to do the insert on the active worksheet. How do I fix it? Thanks so much, Todd Sub InsertRowsAndFillFormulas(Optional vRows As Long) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'type 1 is number If vRows = False Then Exit Sub End If '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 Integer 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 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 |
Insert Rows Macro
Thank you both!
-----Original Message----- This should help. Take out what you don't want. Sub InsertRows() usedrows = Cells(1, "b").End(xlDown).Row x = InputBox("Enter Number of rows to insert") y = InputBox("Enter Date") Rows(usedrows + 1 & ":" & usedrows + x).Insert Range(Cells(usedrows + 2, "a"), Cells(usedrows + 1 + x, "a")) = "Q " & Format(y, "mm/dd/yy") Range(Cells(usedrows, "H"), Cells(usedrows, "J")).Copy _ Range(Cells(usedrows + 1, "H"), Cells(usedrows + 1 + x, "J")) End Sub -- Don Guillett SalesAid Software "Todd" wrote in message ... Hi, I am trying to get a macro to insert rows and fill formulas. I want to hit a button, have a message box open which asks "How Many Rows To Insert" and then begin inserting and filling formulas. Unfortunatly my eyes are bigger than my VBA skill. I have this macro which I have adapted before (thanks to this group). I only want to do the insert on the active worksheet. How do I fix it? Thanks so much, Todd Sub InsertRowsAndFillFormulas(Optional vRows As Long) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09- 02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'type 1 is number If vRows = False Then Exit Sub End If '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 Integer 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 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 . |
Insert Rows Macro
glad it helped
-- Don Guillett SalesAid Software "Todd" wrote in message ... Thank you both! -----Original Message----- This should help. Take out what you don't want. Sub InsertRows() usedrows = Cells(1, "b").End(xlDown).Row x = InputBox("Enter Number of rows to insert") y = InputBox("Enter Date") Rows(usedrows + 1 & ":" & usedrows + x).Insert Range(Cells(usedrows + 2, "a"), Cells(usedrows + 1 + x, "a")) = "Q " & Format(y, "mm/dd/yy") Range(Cells(usedrows, "H"), Cells(usedrows, "J")).Copy _ Range(Cells(usedrows + 1, "H"), Cells(usedrows + 1 + x, "J")) End Sub -- Don Guillett SalesAid Software "Todd" wrote in message ... Hi, I am trying to get a macro to insert rows and fill formulas. I want to hit a button, have a message box open which asks "How Many Rows To Insert" and then begin inserting and filling formulas. Unfortunatly my eyes are bigger than my VBA skill. I have this macro which I have adapted before (thanks to this group). I only want to do the insert on the active worksheet. How do I fix it? Thanks so much, Todd Sub InsertRowsAndFillFormulas(Optional vRows As Long) ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm ' Insert Rows -- 1997/09/24 Mark Hill 'Dim vRows As Integer ' row selection based on active cell -- rev. 2000-09- 02 David McRitchie ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows < 1 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'type 1 is number If vRows = False Then Exit Sub End If '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 Integer 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 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 . |
| All times are GMT +1. The time now is 02:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com