I have a spreadsheet where I need to insert 96 rows between rows A1 and A2,
each cell contains a date. I then need to copy a value from cell B1 to fill
the 96 cells just created into column B. I need to be able to repeat this
for all the dates in column A. This can be a large list of dates so need a
macro that will automate the process. Any suggestions as I cannot record a
macro to do this and am not a VB expert.
Thanks in advance
--
pmm

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
Cells(i + 1, "A").Resize(96).EntireRow.Insert
Cells(i + 1, "B").Resize(96).Value = Cells(i, "B").Value
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"paulM" wrote in message
...
I have a spreadsheet where I need to insert 96 rows between rows A1 and
A2,
each cell contains a date. I then need to copy a value from cell B1 to
fill
the 96 cells just created into column B. I need to be able to repeat this
for all the dates in column A. This can be a large list of dates so need
a
macro that will automate the process. Any suggestions as I cannot record
a
macro to do this and am not a VB expert.
Thanks in advance
--
pmm

Thank you Bob it works fine.
Brilliant forum

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
Cells(i + 1, "A").Resize(96).EntireRow.Insert
Cells(i + 1, "B").Resize(96).Value = Cells(i, "B").Value
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"paulM" wrote in message
...
I have a spreadsheet where I need to insert 96 rows between rows A1 and
A2,
each cell contains a date. I then need to copy a value from cell B1 to
fill
the 96 cells just created into column B. I need to be able to repeat this
for all the dates in column A. This can be a large list of dates so need
a
macro that will automate the process. Any suggestions as I cannot record
a
macro to do this and am not a VB expert.
Thanks in advance
--
pmm