VBA Program needed to insert page breaks and insert header when value in row changes

[dinesababu1]

Dear Excel Community

I really need your help! I have a ton of bills which I need to submit for payment and I need help to get the data into the format I require.

Basically I have a spreadsheet with list of company codes in Column A. I need a macro that can insert a particular selection when the value in column A changes.

I have no issue copying the selection, but when I try to insert the selection the macro does not work.

The following is what I am trying, but it isn't working, can someone please help?
Sheets("Formula").Select
Rows("7:14").Select
Selection.Copy
Sheets("Final Format").Select

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

 

[Rick Rothstein]

Does this macro do what you want...

Sub InsertIntermediateHeaders()
  Dim R As Long
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 9 Step -1
    If Cells(R, "A").Value <> Cells(R - 1, "A").Value Then
      Rows(R).Resize(8).Insert
      Sheets("Formula").Rows("7:14").Copy Cells(R, "A")
    End If
  Next
End Sub

 

[dinesababu1]

Rick, You are AWESOME!

The coding worked!

I could use a little more help if you do not mind....

I could use a coding that inserts a page break right above the selection we just inserted ...

Furthermore, Though I need a header every time the value changes... I also need to insert a selection every 40 rows if the value stays the same. So lets say column A stays the same for 60 rows, I need to insert the selection right after the 40th row... (This way so when I print no page will be too crowded.)

Do you think you can add additional coding to help me with this?