Insert Rows Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a insert rows macro which carries formulas when you insert a row. I have two relative formulas and one absolute formula. One of my relative formulas is =IF(D11>=1, A11+1,""), whenever I add rows with my macro D11 and A11 stay the same. I need them to change for the next row.
 
Jason,

The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

Jason Watts said:
I am using a insert rows macro which carries formulas when you
Click to expand...
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11>=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.
 
Thanks Bernie, but when I insert the new row the only info I want copied are my formulas, I already have a macro that works for that, except the IF formula copies like it is absolute.

----- Bernie Deitrick wrote: -----

Jason,

The macro below shows the proper technique, copying the row of the
activecell and inserting a new row at the activecell.

HTH,
Bernie
MS Excel MVP

Sub InsertCopiedRow()

' Macro 1/5/2004 by Bernie Deitrick

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

Jason Watts said:
I am using a insert rows macro which carries formulas when you
Click to expand...
insert a row. I have two relative formulas and one absolute formula.
One of my relative formulas is =IF(D11>=1, A11+1,""), whenever I add
rows with my macro D11 and A11 stay the same. I need them to change
for the next row.
 
Post your code and maybe someone can see what the problem is.

--
Regards,
Tom Ogilvy

Jason Watts said:
Thanks Bernie, but when I insert the new row the only info I want copied
Click to expand...
are my formulas, I already have a macro that works for that, except the IF
formula copies like it is absolute.
 
Sub InsertRowsAndFillFormulas()
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]>
'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

Hope someone can help!!

----- Tom Ogilvy wrote: -----

Post your code and maybe someone can see what the problem is.

--
Regards,
Tom Ogilvy

Jason Watts said:
Thanks Bernie, but when I insert the new row the only info I want copied
Click to expand...
are my formulas, I already have a macro that works for that, except the IF
formula copies like it is absolute.
 
Jason,

Your subroutine worked fine for me. To simplify and find the problem,
try this:

Sub InsertCopiedRow()
ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow.SpecialCells(xlCellTypeConstants).ClearContent
s
End Sub

If you still get 'absolute' style formulas, then something else may be
wrong.

HTH,
Bernie
MS Excel MVP

Jason Watts said:
Thanks Bernie, but when I insert the new row the only info I want
Click to expand...
copied are my formulas, I already have a macro that works for that,
except the IF formula copies like it is absolute.
 
The formula below is where the 4 & 5 are at whenever I add a new row with my macro the D9 & A9 should become D10 & A10, they don't they stay the same.
=IF(D9>=1, A9+1,""
4 Flash 10' 10 2 20.00 $400.00
4 50 3 150.00 $3,000.00
5
5 60 4 240.00 $4,800.00
 
But: did you try my macro, and did it make a difference or not?

HTH,
Bernie
MS Excel MVP

Jason Watts said:
The formula below is where the 4 & 5 are at whenever I add a new row
Click to expand...
with my macro the D9 & A9 should become D10 & A10, they don't they
stay the same.
 
The macro worked fine for me. It adjusted the row references in the formula
you showed: =IF(D11>=1, A11+1,"")

Regards,
Tom Ogilvy
 
I went back over every thing again and I think I didn't explain my problem right. The Macro does adjust the row references in the new inserted row however the existing rows after do not adjust thus my count is off ie. 1,2,3,3,4. How do I make the second 3 a 4. Hope this makes more sence.
 
Jason,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myCell As Range

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow.SpecialCells(xlCellTypeConstants).ClearContent
s

For Each myCell In ActiveCell(0).EntireRow.SpecialCells(xlFormulas)
myCell.Copy Range(myCell, Cells(65536, myCell.Column).End(xlUp))
Next myCell
Application.CutCopyMode = False
End Sub

Jason Watts said:
I went back over every thing again and I think I didn't explain my
Click to expand...
problem right. The Macro does adjust the row references in the new
inserted row however the existing rows after do not adjust thus my
count is off ie. 1,2,3,3,4. How do I make the second 3 a 4. Hope
this makes more sence.
 
Bernie, it works except there is a bug in the following line,
myCell.Copy Range(myCell, Cells(65536, myCell.Column).End(xlUp))
 
Worked for me.

Perhaps you're using Excel 95? Try

myCell.Copy Range(myCell, Cells(5000, myCell.Column).End(xlUp))

If that doesn't work, then change to this and run the whole macro to
help debug:

Sub TryNow()
Dim myCell As Range

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown

ActiveCell(2).EntireRow. _
SpecialCells(xlCellTypeConstants).ClearContents

For Each myCell In ActiveCell(0).EntireRow.SpecialCells(xlFormulas)
MsgBox myCell.Address
myCell.Copy
MsgBox Range(myCell, Cells(65536, myCell.Column).End(xlUp)).Address
Range(myCell, Cells(65536, myCell.Column).End(xlUp)). _
PasteSpecial (xlPasteFormulas)
Next myCell
Application.CutCopyMode = False
End Sub

Report specifically what errors you get.

HTH,
Bernie
MS Excel MVP
 
Bernie, I am using excel xp, I tried your new macro and got a Run-time error '1004': No cells were found. Hope this helps.
 
Jason,

If no cells were found, then either you don't have any cells with
formulas, or you don't have any cells with constants. In either case,
after the line:

Dim myCell As Range

put this line:

On Error Resume Next.

HTH,
Bernie
MS Excel MVP

Jason Watts said:
Bernie, I am using excel xp, I tried your new macro and got a
Click to expand...
Run-time error '1004': No cells were found. Hope this helps.
 
If a macro terminates after turning calculation off, but before
the macro would turn it back on again, you can end up
with calculation turned off. Same applies to an addin.
The only difference is that addins are usually written to be
more bulletproof -- doesn't mean they always are. The usual
cause is from testing your own macros and they blow up.

If you have a lot of problems with something turning calculation
off and don't know what it is you might look at the task bar and
see if you see calculate or something similar stuck there after
everything has completed. A better solution toward identifying
when it happens is to test for it -- see Auto_open macro in
http://www.mvps.org/dmcritchie/excel/slowresp.htm#addins

If you look at the page referenced in your original macro you
would see another technique specifically addressing rows
added to the bottom (extending). Though I think this was
added in Excel 2000 -- I use Excel 2000 and never used Excel 97.

Extended Formats and Formulas (#extended)
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#extended

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
 
Jason,

Ooooops! I accidentally hit a period after Next

On Error Resume Next.
should be
On Error Resume Next

Sorry about that.

Bernie
MS Excel MVP
 
~× said:
*I am using a insert rows macro which carries formulas when yo
insert a row. I have two relative formulas and one absolute formula
One of my relative formulas is =IF(D11>=1, A11+1,""), whenever I ad
rows with my macro D11 and A11 stay the same. I nee
d them to change for the next row. *
Click to expand...

Try,
=IF($D11>=1, $A11+1,"") to reflect different rows same column,
=IF(D$11>=1, A$11+1,"") to reflect different columns same row,
=IF($D$11>=1, $A$11+1,"") to maintain constant cell
 
Back
Top