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

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

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

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

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