distribute data to every other cell
 

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.

distribute data to every other cell
 

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.

"mikeweasel" wrote:

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.

distribute data to every other cell
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"mikeweasel" wrote in message
...
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.