Inserting a row

[Guest]

Good day everyone,

On an excel spreadsheet, I'd like to insert a row every 16 rows. Then I'd
like to insert a copy of the first row and then again an empty row adding a
footer saying Page X of X....

Would someone know how I could write this in VBA ?

Thanks for your time

Denys

 

[Tom Ogilvy]

Sub BB()
Dim i As Long, j As Long
i = 1
j = 1
Do While Not IsEmpty(Cells(i, 1))
i = i + 1
j = j + 1
If j = 17 Then
Rows(i).Insert
j = 1
i = i + 1
End If
Loop
End Sub

inserts the rows. Change i to reflect the first row in the data that you
want separated into rows of 15. For example, if you have

row1 Header
row2
row3 1
.. . .
row18 16

then you would set i to 3

I don't know what you mean by insert a copy of the first row and an empty
row.

The footer is confusing as well. Sounds like you are missing Excel's built
in capabilities If you want to print sections of 16 rows, then put in
pagebreaks instead of rows. Use the rows to repeat at top in the 3rd tab of
page setup to indicate your header row and the blank row.
Then you can use a standard custom footer. (View=>Header and Footer)

Sub BBB()
Dim i As Long, j As Long
i = 3
j = 1
Do While Not IsEmpty(Cells(i, 1))
i = i + 1
j = j + 1
If j = 17 Then
ActiveSheet.HPageBreaks.Add Before:=Rows(i)
j = 1
End If
Loop
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.CenterFooter = "Page &P of &N Pages"
End With
End Sub

 

[David McRitchie]

Or perhaps the builtin in capabilities of Freezing the first row for
display and of repeating the first row during printing.
http://www.mvps.org/dmcritchie/excel/freeze.htm
look at topic #freeze and at topic #printing
Tom did mention the printing so the freeze part for display
is what was still missing.

 

[Guest]

Hi Tom.

Thank you very much...This is going to be extremely helpful..

Have a nice weekend

Denys