trying to create an insert row macro
hey guys,
I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
put this code in a standard module...
'/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
highlight the entire row where he wants to insert a row, right click then
insert row then he can hit ctrl Y to insert more rows at that location if needed then he can highlight the new rows with the old row above it then hit ctrl d That will copy formulas down. "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
Gary,
That worked out SO well! I have been able to apply it to a couple different projects I had been working on. I guess a follow up question would be... is it possible to adapt that code so that it may be used on a protected sheet? THANKS! "Gary L Brown" wrote: put this code in a standard module... '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
Adjusted for protected (but not password protected) worksheets
- see below -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: Gary, That worked out SO well! I have been able to apply it to a couple different projects I had been working on. I guess a follow up question would be... is it possible to adapt that code so that it may be used on a protected sheet? THANKS! '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim blnProtectContents As Boolean Dim blnProtectDrawingObjects As Boolean Dim blnProtectScenarios As Boolean Dim vRows As Long, i As Long Dim strAddress As String, shts() As String Dim sht As Worksheet 'set default for whether worksheet is protected or not blnProtectContents = False blnProtectDrawingObjects = False blnProtectScenarios = False strAddress = Selection.Address 'rev. 2005-08-02 - check if worksheet unprotected ' if it's protected, get various information If Application.ActiveSheet.ProtectContents = True Then blnProtectContents = True If Application.ActiveSheet.ProtectDrawingObjects = True Then blnProtectDrawingObjects = True End If If Application.ActiveSheet.ProtectScenarios = True Then blnProtectScenarios = True End If ActiveSheet.Unprotect If Application.ActiveSheet.ProtectContents = True Then 'not unprotected so stop process Exit Sub End If End If ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select vRows = _ Application.InputBox(prompt:= _ "How many rows do you want to add?" & vbCr & vbCr & _ "Rows will be added UNDERNEATH this row.", _ Title:="Add Rows", _ Default:=1, Type:=1) 'type 1 is number If vRows = False Then Exit Sub 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 ' and go back to original selected cells Worksheets(shts).Select Range(strAddress).Select 'set worksheet back to original protected/unprotected state ActiveSheet.Protect DrawingObjects:=blnProtectDrawingObjects, _ Contents:=blnProtectContents, Scenarios:=blnProtectScenarios End Sub '/=======End of Code==========================/ |
Hi Gary
I have a question which is related to the code below. I used this code and it worked out extremely well. I am using a macro button 'Insert Row' to insert rows and update formulae. I have a worksheet where the data is in A1:D10. I am summing up columns in row 11 (e.g., sum(A1:A10). If I select row 10 (or any cell on row 10) and insert a row using the button, the formulae that sum up the columns dont get updated. (e.g., fomula for coumn A should get updated to sum(A1:A11)). Is there a way to do this? Thanks in advance. "Gary L Brown" wrote: put this code in a standard module... '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
Thanks Gary...
"Gary L Brown" wrote: Unfortunately, this is the way Excel works, whether you insert manually or by macro. A work-around would be to always leave a row between your data and your sum formula. For example: Data is in A1:A10. There is a blank row at A11 Your sum formula is in A12 and is =Sum(A1:A11) Now, if you use the macro on row 10, the sum formula WILL change to reflect the new range. I know this isn't what you ideally are looking for but it will work. Good Luck. HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "sam_c" wrote: Hi Gary I have a question which is related to the code below. I used this code and it worked out extremely well. I am using a macro button 'Insert Row' to insert rows and update formulae. I have a worksheet where the data is in A1:D10. I am summing up columns in row 11 (e.g., sum(A1:A10). If I select row 10 (or any cell on row 10) and insert a row using the button, the formulae that sum up the columns dont get updated. (e.g., fomula for coumn A should get updated to sum(A1:A11)). Is there a way to do this? Thanks in advance. "Gary L Brown" wrote: put this code in a standard module... '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
trying to create an insert row macro
Gary, thanks for the help on the insert row with formulae. I know no VBA but
it works a treat. I'll have to learn VBA! Lel "Gary L Brown" wrote: Unfortunately, this is the way Excel works, whether you insert manually or by macro. A work-around would be to always leave a row between your data and your sum formula. For example: Data is in A1:A10. There is a blank row at A11 Your sum formula is in A12 and is =Sum(A1:A11) Now, if you use the macro on row 10, the sum formula WILL change to reflect the new range. I know this isn't what you ideally are looking for but it will work. Good Luck. HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "sam_c" wrote: Hi Gary I have a question which is related to the code below. I used this code and it worked out extremely well. I am using a macro button 'Insert Row' to insert rows and update formulae. I have a worksheet where the data is in A1:D10. I am summing up columns in row 11 (e.g., sum(A1:A10). If I select row 10 (or any cell on row 10) and insert a row using the button, the formulae that sum up the columns dont get updated. (e.g., fomula for coumn A should get updated to sum(A1:A11)). Is there a way to do this? Thanks in advance. "Gary L Brown" wrote: put this code in a standard module... '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
trying to create an insert row macro
This is very useful for what I want to do also - however I want the new rows
to be placed above the active cell - is this possible? I have tried playing with offset - but have been unsuccessful. Thanks "lel" wrote: Gary, thanks for the help on the insert row with formulae. I know no VBA but it works a treat. I'll have to learn VBA! Lel "Gary L Brown" wrote: Unfortunately, this is the way Excel works, whether you insert manually or by macro. A work-around would be to always leave a row between your data and your sum formula. For example: Data is in A1:A10. There is a blank row at A11 Your sum formula is in A12 and is =Sum(A1:A11) Now, if you use the macro on row 10, the sum formula WILL change to reflect the new range. I know this isn't what you ideally are looking for but it will work. Good Luck. HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "sam_c" wrote: Hi Gary I have a question which is related to the code below. I used this code and it worked out extremely well. I am using a macro button 'Insert Row' to insert rows and update formulae. I have a worksheet where the data is in A1:D10. I am summing up columns in row 11 (e.g., sum(A1:A10). If I select row 10 (or any cell on row 10) and insert a row using the button, the formulae that sum up the columns dont get updated. (e.g., fomula for coumn A should get updated to sum(A1:A11)). Is there a way to do this? Thanks in advance. "Gary L Brown" wrote: put this code in a standard module... '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown ' - Kinneson Corp. 01/17/2001 ' - modification from thread discussion in ' Microsoft.Public.Excel.Programming newsgroup ' on 01/17/2001 ' Insert Rows -- 1997/09/24 Mark Hill ' The original macro is described in ' http://www.geocities.com/davemcritch...l/insrtrow.htm Dim vRows As Long Dim sht As Worksheet, shts() As String, i As Long ' row selection based on active cell -- ' rev. 2000-09-02 David McRitchie ActiveCell.EntireRow.Select 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 'if you just want to add cells and not entire rows ' then delete ".EntireRow" in the following line ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 'insert rows on grouped worksheets ' rev. 2001-01-17 Gary Brown 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 remove the non-formulas -- 1998/03/11 Bill Manville Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht 'reselect original group - Dave McRitchie 01/17/2001 Worksheets(shts).Select End Sub '/=======End of Code==========================/ HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "jgray" wrote: hey guys, I'm trying to create a macro to streamline a process for one of our factory guys. Right now, I'm going down and manually inserting rows into a database and then dragging the needed formulas and information from the rows above down. What I'm trying to do is make it so he can place his cursor, hit the macro key stroke and have that done for him. When I record the macro, I think I am making an absolute cell reference, because when I run the macro it will only instert where I initially inserted the row. How do I create the macro so he can select where he wants it and then have the row inserted and formulas dropped down for him? Thanks in advance for all your help! James |
All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com