Please help with macro

[eriknyc]

I am trying to insert a row (row 1 of sheet 2) in between any row in
sheet1 where the value in column A changes.

I have found code that inserts a blank row but I don't know how to
change it so it copies the row from sheet 2 instead. Thanks in
advance... here is the code so far.

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step
-1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Rows
(lRow).EntireRow.Insert
Next lRow
End Sub

 

[JLGWhiz]

Untested, but see if it does what you want.

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then
Sheets(2).Rows(1).Copy
Rows(lRow).Insert
Next lRow
End Sub

 

[eriknyc]

Thanks for trying but it won't compile it says next without for
error.... I am sure it is easy to fix but I am really new at this.


Thanks,
Erik

 

[eriknyc]

I fixed the complie error... I just pasted it wrong but now it inserts
the formula in the right place but it creates a space in between every
line also. thanks in advance


Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then Sheets(2).Rows
(1).Copy
Rows(lRow).Insert
Next lRow
End Sub

 

[eriknyc]

nevermind... firgured it out... thanks for your help.

 

[JLGWhiz]

Replace the previous version with this one. You need to use a block If
statement.

Sub InsertRowAtChangeInValue()
Dim lRow As Long
For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then
Sheets(2).Rows(1).Copy 'keep on separate line
Rows(lRow).Insert
End If
Next lRow
End Sub