ExcelBanter - Please help with macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Please help with macro (https://www.excelbanter.com/excel-programming/420010-please-help-macro.html)

Please help with macro

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

Please help with macro

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

" wrote:

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

Please help with macro

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
On Nov 14, 12:15*am, JLGWhiz
wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -

Please help with macro

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

On Nov 14, 10:03*am, wrote:
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
On Nov 14, 12:15*am, JLGWhiz
wrote:

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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Please help with macro

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

On Nov 14, 10:50*am, wrote:
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

On Nov 14, 10:03*am, wrote:

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
On Nov 14, 12:15*am, JLGWhiz
wrote:

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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Please help with macro

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

" wrote:

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

On Nov 14, 10:03 am, wrote:
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
On Nov 14, 12:15 am, JLGWhiz
wrote:

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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -