Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dividing data into worksheets by column values
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dividing data into worksheets by column values
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dividing data into worksheets by column values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link values from same cell of 20 worksheets into column on one she | Excel Worksheet Functions | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
Dividing a column | Excel Discussion (Misc queries) | |||
Wrong result on dividing 2 values | Excel Worksheet Functions | |||
DIVIDING TOTAL CELLS IN COLUMN BY 16 TRYING TO CHANGE WEIGHT OZ TO | Excel Worksheet Functions |