MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy a column & paste into consecutive rows


Posted by Lori on December 05, 2001 11:40 AM

I have a column of data that I want to copy into another worksheet. On the other worksheet, I know I can select Paste Special-Transpose to get it into a row. But, is there a way to get it to paste into the first 3 cells in row A, then do the first 3 in row B, and so on, until done?
Thanks again!


Posted by Colo on December 05, 2001 11:26 PM

Hi! How about this?

Sub Test()
'/*Sheets(1) as DataSheet,Sheets(2) as Another worksheet*/
Dim data() As Variant, i As Integer, intCol As Integer
With Application
.ScreenUpdating = False
intCol = .RoundUp(Sheets(1).Cells(1, 256).End(xlToLeft).Column / 3, 0) - 1
ReDim data(0 To intCol)
For i = 0 To intCol
data(i) = Sheets(1).Cells(1, i * 3 + 1).Resize(, 3).Value
Sheets(2).Cells(1, i + 1).Resize(3).Value = .Transpose(data(i))
Next
.ScreenUpdating = True
End With
End Sub

Posted by Lori on December 06, 2001 11:41 AM

I must be doing something wrong, because when I try to run it, nothing happens.
I put the code in my personal.xls.
I have 2 sheets in the work book. Sheet1 is named "Labels". This is where I want to paste into, 3 across. Sheet2 is named "Data". This is where I copy what I want to put on the Labels page.
This is what I did. In your code, I inserted Sheets("Labels") in place of Sheets(1) and Sheets("Data") in place of Sheets(2).
I copy the data I want from the Data sheet, select the Labels sheet and try to run the macro. Nothing happens. If you can help me with this, I would greatly appreciate it!
Thanks.

Posted by Colo on December 06, 2001 5:13 PM

Hi,Lori

:I put the code in my personal.xls.
That's OK...

First of all, I want you to confirm which rows has data.
I assumed the data is input on row(1) of Sheets("Data").
ex. A1,B1,C1,D1,E1..and so on.

if the data is located on where I wrote, and it does not work then
please check the code.

You can run macro to press F8 key, step by step. (on VBE)
And you can confirm what happened now, on the local window and
immediate window of VBE.

If you want, I can send email with sample file for you.

Thanks,

This is a code only changed name of Sheets.
'------------------------------------------------------------------------------
Sub Test2()
Dim data() As Variant, i As Integer, intCol As Integer
With Application
.ScreenUpdating = False
intCol = .RoundUp(Sheets("Data").Cells(1, 256).End(xlToLeft).Column / 3, 0) - 1
ReDim data(0 To intCol)
For i = 0 To intCol
data(i) = Sheets("Data").Cells(1, i * 3 + 1).Resize(, 3).Value
Sheets("Labels").Cells(1, i + 1).Resize(3).Value = .Transpose(data(i))
Next
.ScreenUpdating = True
End With
End Sub
'------------------------------------------------------------------------------

Posted by Lori on December 07, 2001 6:25 AM

Colo,
Thanks for your help on this. This is an awesome site. The info that I want to copy resides in my "Data" Sheet in column G. (G1,G2,G3...)
I want to paste it into Sheet "Labels"; The copied data should get transpose and pasted into the 1st 3 rows of "Labels", cells A1, B1, C1 and then on down until everything from the clipboard is pasted. I hope I'm telling you what you're asking, and if you don't mind sending a sample sheet, that would be awesome. Thanks again.