I have an xls worksheet that has multiple salespeople in a column. Each
sale for a salesperson is in a row, making multiple sales for each
salesperson.

What I want to do is make a worksheet for each salesperson, which would
have the same first row (column heading) as the main worksheet, and
each sale for that salesperson.


This tip seemed to be on the right track, but I don't understand the
code well enough to make it work:

http://tinyurl.com/yuah


(I'm doing it by hand now - Help - getting carpal tunnel syndrome!)

-Mike


---
Message posted from http://www.ExcelForum.com/

Mike,

I adapted it to what I think you want

Sub AfterNamesCopying()
Dim wks As Worksheet, wksData As Worksheet
Dim intRow As Integer, iCol As Integer
Dim strSheet As String
Dim sHead As String

Application.ScreenUpdating = False
Set wksData = ActiveSheet
sHead = wksData.Cells(1, 1).Value
intRow = 2
On Error Resume Next
Do Until IsEmpty(wksData.Cells(intRow, 1))
Worksheets.Add after:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = wksData.Cells(intRow, 1).Value
.Cells(1, 1) = sHead
For iCol = 2 To wksData.Cells(intRow,
Columns.Count).End(xlToLeft).Column
.Cells(iCol, 1) = wksData.Cells(intRow, iCol)
Next iCol
End With
intRow = intRow + 1
Loop

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mikeb1" wrote in message
...
I have an xls worksheet that has multiple salespeople in a column. Each
sale for a salesperson is in a row, making multiple sales for each
salesperson.

What I want to do is make a worksheet for each salesperson, which would
have the same first row (column heading) as the main worksheet, and
each sale for that salesperson.


This tip seemed to be on the right track, but I don't understand the
code well enough to make it work:

http://tinyurl.com/yuah


(I'm doing it by hand now - Help - getting carpal tunnel syndrome!)

-Mike


---
Message posted from http://www.ExcelForum.com/

Thank you very much for improving that code.

The data I begin with is like this:

A505 0262091 368083 SHEPHE SHEPHERD ELECTRICAL
A506 0262092 368347 HUGH HUGHES SUPPLY, INC.
CS 0261493 367768 WB0027 BROOK ELECTRICAL DIST.
CS 0261562 365469 WV0014 VIKING ELECTRIC SUPPLY
CS 0261562 365469 WV0014 VIKING ELECTRIC SUPPLY
CS 0261715 367868 WC0036 CARLYN ELECTRIC
CS 0261719 368168 WC0036 CARLYN ELECTRIC



The A505, A506, etc. values are the salespeople that I want to divid
into worksheets.

Using the function you corrected, I get this in the worksheets:
__________________________________________
for the A505 Worksheet:

Salesman
0262091
368083
SHEPHE
SHEPHERD ELECTRICAL SUPPLY CO.



GOLDSBORO
NC
111203

244

_________________________________________________
For the CS Worksheet:

Salesman
0261493
367768
WB0027
BROOK ELECTRICAL DIST.



LINCOLNSHIRE
IL
102703

156

__________________________________

As you can see, the formatting is all different, and multiple rows ar
not copied. Also, after 7 worksheets, the name is no longer copied..
the worksheets begin to be named "sheet8, sheet9, etc."

Thanks in advance for your help.

-Mik

--
Message posted from http://www.ExcelForum.com