cutting and pasting data

greenleve · May 15th, 2002, 10:51 AM

I am trying to copy data from one sheet and paste it to another in alternate columns. I'm talking about a lot of data, so I'd like to do it all at once, not one data column at a time.

DZarlengo · May 15th, 2002, 11:01 AM

We need more information. For example, if your destination sheet is blank, you could just paste the data then run a quick macro to insert blank columns. If you are pasting into a heavily populated sheet, you could use a VBA loop - but are you pasting entire columns or certain ranges? What specifically are you trying to do?

Mark W. · May 15th, 2002, 11:06 AM

The designated Paste area must be contiguous.

eliW · May 15th, 2002, 11:06 AM

Hi greenleve,
If I understand your request you want to copy data from columns A,B,C etc. To columns A,C,E etc.
Try this idea that was posted to this board in the past.

1) Insert a new row at the top
2) Fill it by 1,2,3…until last column of your range, e.g. A1:BD1
3) Now copy this range (A1:BD1) to the next cell in the row (BE1), you’ll get 2 following sets of sequential numbers
4) Sort your entire range on horizontal orientation (left to right) you can do it by entering Options
5) You’ll get your data in alternate columns
6) You may now delete first row

Eli

[ This Message was edited by: eliW on 2002-05-15 10:36 ]

greenleve · May 16th, 2002, 05:07 AM

I'm pasting specific ranges of data, for example,b6:b26 is one column of data for one specific sample, c6:c26 is the next column of data for another sample, etc...., I'd like to paste those ranges to a sheet where the data is in b6:b26 at about 9-wide and the next column,c, is about 1-wide with a formula relating to that data, then the data from the first sheet in column c6:c26 would go into column d which is 9-wide and column e, 1-wide, would contain the formula, and so on.. Right now I have a template for the second sheet that already has the formula and format for the data, however, if it would be easier to start with a blank sheet and use a macro, I'm willing to try that. I just don't know how to do it. I appreciate the help!!

DZarlengo · May 16th, 2002, 01:10 PM

Looks to me like you need to use a nested loop to write the columns. Remember that you can use "Step" with loops to skip numbers, such as:

For X = 1 to 10
For Y = 2 to 20 Step 2
Range(Cells(X, 1), Cells(X, 26)).Copy
ActiveSheet.Paste Destination:=Range(Cells(Y,1),Cells(Y,26))
Next Y
Next X

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 10:51 AM	#1
greenleve New Member Join Date: May 2002 Posts: 2	I am trying to copy data from one sheet and paste it to another in alternate columns. I'm talking about a lot of data, so I'd like to do it all at once, not one data column at a time.

May 15th, 2002, 11:01 AM	#2
DZarlengo Board Regular Join Date: May 2002 Posts: 63	We need more information. For example, if your destination sheet is blank, you could just paste the data then run a quick macro to insert blank columns. If you are pasting into a heavily populated sheet, you could use a VBA loop - but are you pasting entire columns or certain ranges? What specifically are you trying to do?

May 15th, 2002, 11:06 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	The designated Paste area must be contiguous.

May 15th, 2002, 11:06 AM	#4
eliW Board Regular Join Date: Mar 2002 Posts: 1,805	Hi greenleve, If I understand your request you want to copy data from columns A,B,C etc. To columns A,C,E etc. Try this idea that was posted to this board in the past. 1) Insert a new row at the top 2) Fill it by 1,2,3…until last column of your range, e.g. A1:BD1 3) Now copy this range (A1:BD1) to the next cell in the row (BE1), you’ll get 2 following sets of sequential numbers 4) Sort your entire range on horizontal orientation (left to right) you can do it by entering Options 5) You’ll get your data in alternate columns 6) You may now delete first row Eli [ This Message was edited by: eliW on 2002-05-15 10:36 ]

May 16th, 2002, 05:07 AM	#5
greenleve New Member Join Date: May 2002 Posts: 2	I'm pasting specific ranges of data, for example,b6:b26 is one column of data for one specific sample, c6:c26 is the next column of data for another sample, etc...., I'd like to paste those ranges to a sheet where the data is in b6:b26 at about 9-wide and the next column,c, is about 1-wide with a formula relating to that data, then the data from the first sheet in column c6:c26 would go into column d which is 9-wide and column e, 1-wide, would contain the formula, and so on.. Right now I have a template for the second sheet that already has the formula and format for the data, however, if it would be easier to start with a blank sheet and use a macro, I'm willing to try that. I just don't know how to do it. I appreciate the help!!

May 16th, 2002, 01:10 PM	#6
DZarlengo Board Regular Join Date: May 2002 Posts: 63	Looks to me like you need to use a nested loop to write the columns. Remember that you can use "Step" with loops to skip numbers, such as: For X = 1 to 10 For Y = 2 to 20 Step 2 Range(Cells(X, 1), Cells(X, 26)).Copy ActiveSheet.Paste Destination:=Range(Cells(Y,1),Cells(Y,26)) Next Y Next X