Please help with macro

E

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
 
J

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
 
E

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
 
E

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top