Copy and paste every x number of cells

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

So I think I am stuck and need some help.
I have a list of numbers and want to duplicate a number every so many cells
keeping the duplicate in the list. I have figured out how to insert 2 blank
lines every 20 cells but now need to copy the cell after the blank cells and
paste it into the first blank cell

i.e I have inserted 2 blank cells at A10 and A11 moving the list down, now
I want to copy the contents of cell A132 (the old A10) and paste it in the
blank A10.

I am sure there is an easy way to do this but I am stuck….any help would be
great!

Thanks
 
Mike,

Here is a macro that should do the job for you.

Good Luck.

***********************************

Option Explicit

Sub InsertRows_n_CopyCell()

'Select the starting row ...
'this example assumes it is cell A7
Range("A7").Select

'Start a Looping procedure
Do While ActiveCell.Value > " "

'Move down 20 rows
ActiveCell.Offset(20).Select

'Insert 2 rows by
'Selecting the current row and
'the row below the current row
Range(ActiveCell, ActiveCell.Offset(1)).Select
Selection.EntireRow.Insert , _
CopyOrigin:=xlFormatFromLeftOrAbove

'Copy the data that is now down 2 rows
'into the Active Cell
ActiveCell.Value = ActiveCell.Offset(2).Value

'Move the active cell down 2 rows
ActiveCell.Offset(2).Select

Loop

End Sub
 
Back
Top