macro to add a row
 

Good afternoon!

I know there are several posts already about this subject, but I can't seem
to make any of the previous answers work.

I am trying to insert a row after a given number defined by "INSERTPT" then
copy data into the new row. (Column A has a list of record numbers...INSERTPT
is a user defined record number)

With the following code, the data gets pasted wherever the last selection
was made in that worksheet. (Most of the time, the previously run macro will
have the user defined record number highlighted, so this macro pastes right
over it, rather than adding a row and pasting under it.)

Thanks for any help!

Sheets("Update TEMP").Select
Range("A3").Select
INSERTPT = ActiveCell.Value
Sheets("Update TEMP").Select
Rows("8:8").Select
Range("I8").Activate
Selection.Copy

Sheets("Incom Inspect Report (IIR)").Select

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = iLastRow To 1 Step 1
If Cells(i, 1).Value = INSERTPT Then
Rows(i + 1).EntireRow.Insert


End If

Next i


Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

macro to add a row
 

Your code has this line that assigns a value to INSERTPT:
INSERTPT = ActiveCell.Value
.... and later, an IF statement that compares a cell value to INSERTPT:
If Cells(i, 1).Value = INSERTPT Then

Is it possible the value in Cells(i, 1).Value never equals INSERTPT?

The other thing I notice is this line
For i = iLastRow To 1 Step 1
In order to get this structure to run, I had to change it to this:
For i = iLastRow To 1 Step -1
Is that a similar problem for you?

macro to add a row
 

Thank you so much..... I did have INSERTPT looking at the wrong cell...should
have been A2.....

(I put the one spot back to a -1...that was just me trying to mess with
things to get it to work before.)
But....

It is now adding a row directly beneath and filling it with what looks like
a paste (but not as values) leaving formulas and pasting as values over the
original record number.

Any ideas?????

"Dave O" wrote:

Your code has this line that assigns a value to INSERTPT:
INSERTPT = ActiveCell.Value
.... and later, an IF statement that compares a cell value to INSERTPT:
If Cells(i, 1).Value = INSERTPT Then

Is it possible the value in Cells(i, 1).Value never equals INSERTPT?

The other thing I notice is this line
For i = iLastRow To 1 Step 1
In order to get this structure to run, I had to change it to this:
For i = iLastRow To 1 Step -1
Is that a similar problem for you?

macro to add a row
 

It is now adding a row directly beneath and filling it with what looks like
a paste (but not as values) leaving formulas and pasting as values
over the
original record number.
<<<
I'm not sure I follow this: but I notice your code has the paste
special instruction OUTSIDE the "if you find the right value then
insert a line" structure. Try this instead:

For i = iLastRow To 1 Step 1
If Cells(i, 1).Value = INSERTPT Then
Rows(i + 1).EntireRow.Insert
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End If
Next i