Hi Charlie,

When I run this code, I get a Run-Time error '9':
Subscript out of range

When I go into the code, this line is highlighted in yellow:

Sheets("ColData").Select

Sorry to be a bother.

"Charlie" wrote:


Hi,

Try this line instead and put it all on one line:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

He

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub


--------------------


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

Mishelley;180461 Wrote:
Thank you for your help, Charlie. When I copy/paste your code and try
to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need
transferred
into
the second worksheet as rows (records) of data. I need to do this
beginning
with Column B until there is a blank column. I am using a macro.
How
can I
automate this event so that only columns with data are transferred
to
the
second sheet?

Thank you,
Mishelley


--
Charlie

------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=49825)




--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825