Insert blanks, but populate new blank rows in one column

[Pierre]

I'm using this code to add 3 blank rows between existing records. How
might I populate the new cells between records just in column B with a
predefined group of values located on another worksheet? These will
not change between rows, but need to be inserted in all the new rows,
in column B. Have tried highlighting the column, F5 for going to
special, blanks, and pasting. . .it works intermittently (getting
frequent paste same size and shape errors), and I'm afraid I need
something more reliable. Thoughts? Many thanks.

Pierre

Sub Insert3Rows_into_927ver2()
Application.ScreenUpdating = False
Sheets("927VER2").Select
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 2 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True

End Sub

 

[Jim Cone]

Sub Insert3Rows_into_927ver_R1()
Application.ScreenUpdating = False
Sheets("927VER2").Select
Dim numRows As Integer
Dim r As Long
Dim vArr() As String
Dim rRows As Range

ReDim vArr(1 To 3, 1 To 1)
vArr(1, 1) = "Larry"
vArr(2, 1) = "Moe"
vArr(3, 1) = "Curly"

r = Cells(Rows.Count, "A").End(xlUp).Row - 1
numRows = 3
For r = r To 2 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Set rRows = Range(Rows(r + 1), Rows(r + 3)).Columns
rRows.Columns(2).Value = vArr()
Next r
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA
xlCompanion Excel add-in: http://tinyurl.com/XLCompanion

..
..
..

"Pierre" <[email protected]>
wrote in message
I'm using this code to add 3 blank rows between existing records. How
might I populate the new cells between records just in column B with a
predefined group of values located on another worksheet? These will
not change between rows, but need to be inserted in all the new rows,
in column B. Have tried highlighting the column, F5 for going to
special, blanks, and pasting. . .it works intermittently (getting
frequent paste same size and shape errors), and I'm afraid I need
something more reliable. Thoughts? Many thanks.

Pierre

Sub Insert3Rows_into_927ver2()
Application.ScreenUpdating = False
Sheets("927VER2").Select
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 2 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True

End Sub

 

[Pierre]

Jim, aside from nailing this one to a T, (it works absolutely
perfectly), you soitenly made my day with your choice of woids to
insert into the fields. Sometimes, I just try to think and nuthin'
happens. Thanks so much for the rescue.

Pierre

 

[Jim Cone]

You are welcome.
'--
Jim Cone




Jim, aside from nailing this one to a T, (it works absolutely
perfectly), you soitenly made my day with your choice of woids to
insert into the fields. Sometimes, I just try to think and nuthin'
happens. Thanks so much for the rescue.
Pierre

 

[Pierre]

John,

One little thing. .

It misses loading the very last "Parent" record, and doesn't populate
the rows following it with the group of 3, just on that one. Can you
revisit, and tell me what to modify?

Many thanks again.

Pierre

 

[Jim Cone]

Remove the "- 1" at the end of...
r = Cells(Rows.Count, "A").End(xlUp).Row - 1



"Pierre" <[email protected]>
wrote in message
Jim,

One little thing. .

It misses loading the very last "Parent" record, and doesn't populate
the rows following it with the group of 3, just on that one. Can you
revisit, and tell me what to modify?

Many thanks again.

Pierre

 

[Pierre]

Thank you!

Pierre