Thread: help urgent

hey guys

hows it going? I need your help once again....i have an excel file with loads of data

ie.

A ....G H I J K L
1 10465 Jason Brad Mike Jeff Heather Lebron

2 1076 Keith Jay Fred David Young Pat

and it goes all the way down to A4056.....however on a seperate sheet I need this information copied like this

SHEET 2

A B
1 10465 Jason
Brad
Mike
Jeff
Heather
Lebron

2 1076 Keith
Jay
Fred
David
Young
Pat

once again, it goes all the way down to A4056. is there a way either thru macro or thru formula for me to do this instead of copying and pasting special? help will be much appreciated; please and thank you. hope you follow what I was trying to get at.....

How about a macro?

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim RngToCopy As Range

Set curWks = Worksheets("Sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 2 'headers in row 1????
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
Set RngToCopy _
= .Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
RngToCopy.Copy
newWks.Cells(oRow, "B").PasteSpecial Transpose:=True
oRow = oRow + RngToCopy.Cells.Count
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


daroc wrote:
>
> hey guys
>
> hows it going? I need your help once again....i have an excel file with
> loads of data
>
> ie.
>
> A ....G H I J
> K L
> 1 10465 Jason Brad Mike Jeff
> Heather Lebron
>
> 2 1076 Keith Jay Fred David
> Young Pat
>
> and it goes all the way down to A4056.....however on a seperate sheet I
> need this information copied like this
>
> SHEET 2
>
> A B
> 1 10465 Jason
> Brad
> Mike
> Jeff
> Heather
> Lebron
>
> 2 1076 Keith
> Jay
> Fred
> David
> Young
> Pat
>
> once again, it goes all the way down to A4056. is there a way either
> thru macro or thru formula for me to do this instead of copying and
> pasting special? help will be much appreciated; please and thank you.
> hope you follow what I was trying to get at.....
>
> --
> daroc
> ------------------------------------------------------------------------
> daroc's Profile: http://www.excelforum.com/member.php...o&userid=30753
> View this thread: http://www.excelforum.com/showthread...hreadid=506753

--

Dave Peterson

I am having trouble running the macro....if i send you a sample of what i am doing on excel....can you pls/kindly make the macro for me...and ill learn it from that and implement it to the rest? let me know pls...ill attach the file