Transpose Macro and Data from web

[chrisscotty]

Hi,

I have a list that I want to transpose. I have several hundred entries that are all in the A column that I want to transpose to rows so I can import in to a contact manager (csv file tab delimited). Then again, if there is any way of transferring the data without transposing to rows t then that would be even better =-0

The structure is as follows:

Name
Company:
E-mail:
Work Phone:
Fax:
Chapter:
Title:

So the data is in A1 to A8 (A8is blank), and I want to transpose to either the same worksheet so B2 to I7 or go to another worksheet (does not really matter to me).

Also, Does anyone know of a program that interfaces with internet explorer and excel that would allow me to get data from a database in explorer and then paste the data in excel as a macro ?.

I have a database that I want to enter but it only lets me get one entry at a time.
Thanks For Your help !.

 

[Corticus]

I think you need a pivot table, that will transpose rows. If you know how to use them, great, if not, hopefully soemone will eleborate, because I'm not very proficient with them.

HTH
Corticus

 

[Jim North]

Hi. This will move the data from column A to columns B thru I.

You will need to determine the max number of rows to end the job... and after it is done, delete column A.

Sub TransposeData()
'
' TransposeData
'
Dim rwIndex As Long
Dim colIndex As Long
Dim setfrom As Long
Dim setto As Long
Dim NewrowNbr As Long

NewrowNbr = 1

setfrom = 1
setto = 8
rwIndex = 1

Do
colIndex = 2
Do Until colIndex > 9
Cells(NewrowNbr, colIndex).Value = Cells(rwIndex, 1).Value
colIndex = colIndex + 1
rwIndex = rwIndex + 1
Loop
NewrowNbr = NewrowNbr + 1
Loop
End Sub

 

[Jim North]

Here's the whole thing... including the delete on Column A:

Option Explicit

Sub TransposeData()
'
' TransposeData
'
Dim rwIndex As Long
Dim colIndex As Long
Dim NewrowNbr As Long
Dim RowCount As Long

NewrowNbr = 1
rwIndex = 1

'Get the number of rows used
RowCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

' loop thru all the data rows
Do Until rwIndex >= RowCount

' use the do statement to load columns 2 thru 9 (B thru I)
colIndex = 2
Do Until colIndex > 9
Cells(NewrowNbr, colIndex).Value = Cells(rwIndex, 1).Value
colIndex = colIndex + 1 'move over one column
rwIndex = rwIndex + 1 'read the next row of data
Loop
NewrowNbr = NewrowNbr + 1 'move to the next new data row
Loop
Columns(1).Delete 'delete column A

End Sub

 

[chrisscotty]

Great !..
That works perfectly.
I am wondering where I went wrong. What I did was copy paste special transpose and then record it, but it would always overwrite it. I have a bunch of different lists with different amounts of data in the A column.

How can I record without actually writing in VB?

By the way, anyone out there know how to use macros with Explorer with excel so you can cut and paste record by record (each record is on a different page ).

Thanks Again !