Insert Copied data every x row 1

[binaryfingers]

Hi All

Skip, I found this code provided by you previously to insert a new line every x number of rows.

Can it be modified so that it inserts row 1 (containing header data) each time? I need to insert it every 72nd line.

Sub InsertEmptyRows()
'this is generally not a good idea
'a better choice is to change the row height
rStart = InputBox("Start in what row?")
If Not IsNumeric(rStart) Then Exit Sub
rEnd = InputBox("How many rows?")
If Not IsNumeric(rEnd) Then Exit Sub
For r = rStart + 5 To CInt(rEnd) + CInt(rStart) + 1 Step 6
Cells(r, 1).EntireRow.Insert shift:=xlUp
Next
End Sub

Hope you or somebody can help save me a lot of time!

Thanks,

 

[SkipVought]

Hi,

Is the REASON for this for PRINTING?

Why not use File/Page Setup - Sheet Tab -- Rows to repeat at top.

Skip,

_[red][/red]

 

[binaryfingers]

Hi Skip

No unfortunately its not, (wish it was!)

Its so that the data can be sucked up into another MS program for data analysis and history.

I have 72 lines of data multiplied by 148 'sections', which requires the header to be inserted in between each start of new section, and the header is the 'key' for the other program to identify new data.

HTH explain the reasoning..

Thanks for the quick response.

 

[SkipVought]

How about Step 72 in the for...Next, starting with the FIRST row and, assuming that A1 is contiguous with the table, ending with...

lLastRow = [A1].currentregion.rows.count

Skip,

_[red][/red]

 

[ck1999]

Try this code

copyrow = Range("1:1")
For a = 1 To 148
insrow = a * 72 + a
Range(insrow & ":" & insrow).Insert
Range(insrow & ":" & insrow).Value = copyrow
Next a

ck1999

 

[binaryfingers]

ck1999

Is this it?
Sub InsertHeaderRows()
copyrow=range("1:1")
For a = 1 to 148
insrow=a*72+a
range(insrow&":"&insrow).insert
range(insrow&":"&insrow).value=copyrow
Next a
End Sub

Thanks to you and Skip for your help! Much appreciated!

 

[SkipVought]

On second thot, do the loop BOTTOM UP as every time you insert a row, it ADDS to the TOTAL row count.

for lRow = lLastRow to lFirstRow Step -72

Next

Skip,

_[red][/red]

 

[binaryfingers]

Skip

Very valid point!

Thanks,

 

[ck1999]

yeah did that work

 

[binaryfingers]

ck1999

Its the first real time doing something like this...

I inserted into the microsoft visual basic editor, then clicked run and got a compile error, syntax error.

Im sorry about being such a 'newbie'

 

[SkipVought]

syntax error on WHAT statement?

Skip,

_[red][/red]

 

[binaryfingers]

Skip

this was the line that was highlighted in Yellow..

Sub InsertHeaderRows()

 

[ck1999]

place spaces on both sides of the & signs

 

[binaryfingers]

ck1999

That got rid of the syntax! Thanks,

And it worked, Thanks!

excellent, thanks so much, I can now modify it to work with other numerical inserts!

Perfect!