Needs to Transpose Large List

[The Patriot]

Hey, I'm trying to put all the names (along with their address, university,
etc) located at http://www.asc41.com/director/frame.htm into an excel
spreadsheet where I have one name (and all the rest of the information) in
one row. Is there an easy way to do this rather and just copy and pasting and
then transposing each individual name into the spreadsheet?

Thanks,
-Nick

 

[Robert24]

This seems to work. Copy the addresses from the website into col A of sheet1
Place the cursor on the top address and run this macro. It will copy that
address to Sheet2. Repeat running the macro for each address.
As your adreesses are of varying lengths you don't get all the email
adresses in one column but you can fix this either before or after you run
the macro.

Hope this helps


Selection.CurrentRegion.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub

 

[Gord Dibben]

Tested with a gaggle of adresses from the site pasted to Column A

After running, delete Column A

Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data........must have a blank row between
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim i As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
i = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
i = i + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(i, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP