Hi,
Can anybody help me with this little beauty? I am a first time loop guy
so not sure what to do here... I have a table of data 8 columns wide and
normally 40 rows high. I want to copy rows A1 to H1 and
transpose them into a single column (e.g. column N)then take the values
in A2 to H2 and transpose them into the same column underneath the
previously transposed column (N). What I am trying to achieve is to
take this table of data and turn it into a single serial string. Also
when the macro loop first starts it needs to check the column it is
transposing to (e.g. N) does not already have data in it, if it does
then it needs to transpose to an adjacent column.

Narly...yes?? Any help very much appreciated


Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=539963

Not sure if this will help but I had a similar situation a while back (when I
ran my macro it could not find the end of the previous data and thus I copied
over existing data.) A fellow responded to my problem with this reply (and
it worked for me)

'Tim,

Every place where you have

Selection.End(xlDown).Select
Range("?28").Select

Change it to

Selection.End(xlDown)(2).Select


"simonsmith" wrote:


Hi,
Can anybody help me with this little beauty? I am a first time loop guy
so not sure what to do here... I have a table of data 8 columns wide and
normally 40 rows high. I want to copy rows A1 to H1 and
transpose them into a single column (e.g. column N)then take the values
in A2 to H2 and transpose them into the same column underneath the
previously transposed column (N). What I am trying to achieve is to
take this table of data and turn it into a single serial string. Also
when the macro loop first starts it needs to check the column it is
transposing to (e.g. N) does not already have data in it, if it does
then it needs to transpose to an adjacent column.

Narly...yes?? Any help very much appreciated


Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=539963

Simon
This macro will do what you want. As written, this macro will paste to
that column that is one column to the right of the last column that has an
entry in row 1.
In each row of your data, this macro will copy columns A:H. The number of
rows of data that you have is immaterial.
HTH Otto
Sub TransposeData()
Dim ColNum As Long
Dim RngColA As Range
Dim i As Range
Application.ScreenUpdating = False
ColNum = Range("IV1").End(xlToLeft).Offset(, 1).Column
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA
Range(i, Cells(i.Row, 8)).Copy
If Cells(Rows.Count, ColNum).End(xlUp).Row = 1 Then
Cells(Rows.Count, ColNum).End(xlUp) _
.PasteSpecial Transpose:=True
Else
Cells(Rows.Count, ColNum).End(xlUp).Offset(1) _
.PasteSpecial Transpose:=True
End If
Next i
Application.ScreenUpdating = True
End Sub
"simonsmith" wrote
in message ...

Hi,
Can anybody help me with this little beauty? I am a first time loop guy
so not sure what to do here... I have a table of data 8 columns wide and
normally 40 rows high. I want to copy rows A1 to H1 and
transpose them into a single column (e.g. column N)then take the values
in A2 to H2 and transpose them into the same column underneath the
previously transposed column (N). What I am trying to achieve is to
take this table of data and turn it into a single serial string. Also
when the macro loop first starts it needs to check the column it is
transposing to (e.g. N) does not already have data in it, if it does
then it needs to transpose to an adjacent column.

Narly...yes?? Any help very much appreciated


Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile:
http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=539963

Thanks Otto this works superbly - exactly as I was wanting it to and
gets rid of one of my major headaches. The other issue I have is I
need to collect this data from all of 30 worksheets (at starting point
B6 of each sheet) of a workbook x then put it into columns in a
separate workbook (or same workbook new sheet). No stress if you dont
want to answer this one. Thanks again


I owe you beer

Regards

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=539963