distribute data to every other cell

[Guest]

OK, I have a row of data, say a1:a40, and I want to copy/paste it elsewhere
in my spreadsheet, but I want to have it go to b1:b79, on every other cell,
i.e. b1, b3, b5, etc. Is there an easy way to do this? Also this data uses
a lot of formulas referring to other cells in the same row, so that has to be
preserved, ie. a1 might be a2+a3, so when it's moved b1 will have to be b3+b5.

one solution i see is copy the data over to a42:a81, then start inserting
columns between each piece of data, then go ahead and move the new row down
but i was hoping to avoid that.

 

[Guest]

One thing you can try is to create a helper column and insert a sequence of
odd numbers (1,3,5...) into this column next to your data. Then in the 40
cells below this, insert a sequence of even numbers (2,4,6...). Then sort
both columns by the helper column index number and the valid data will fall
into place in every other cell of the column.

 

[Bernard Liengme]

Copy A1:A40 to B1, then use this macro.

Sub ShiftRight()
Range("B2").Select
Selection.Insert Shift:=xlToRight
For j = 1 To 38
ActiveCell.Offset(columnOffset:=2).Activate
Selection.Insert Shift:=xlToRight
Next j
End Sub

If unsure of VBA see David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
best wishes