excel rows

Posted by Sg on December 05, 2001 11:45 AM

Hi
I have a excel spreadsheet with :

Name Address Email Phone SSN
xxx xxx xxx xxx xxx
aa aaa aaa aaa aaa

I want to make a new xls sheet each time displaying
Name xxx
Address xxx
Email xxx
PHone xxx

Then next sheet

Name aaa
Address aaa
Email aaa
PHone aaa

the sheets shd be called xxx, aaa
also I dont want ssn to be displayed on xxx , aaa.

I am new to this so any help is appreciated

Posted by Katt on December 05, 2001 1:37 PM

This might not be the best way, but you could do a copy, paste special transpose and delete the data that you do not want included.
KS

Posted by Colo on December 05, 2001 6:54 PM

Hi. Try this code.

Sub Test()
'Before you run this code, please select data sheet.
Dim varData As Variant, arrTitle, intCnt As Integer, i As Integer
arrTitle = Array("Name", "Address", "Email", "Phone")
varData = Range("A2", Range("E65536").End(xlUp)).Value
For intCnt = LBound(varData, 1) To UBound(varData, 1)
Sheets.Add.Name = varData(intCnt, 1)
Range("A1").Resize(4).Value = Application.Transpose(arrTitle)
For i = 1 To 4
Range("B1").Offset(i - 1).Value = varData(intCnt, i)
Next
Next
MsgBox "...Done"
End Sub