| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
new sheet
i did not create the worksheet i am working with. The data is organized so
the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
new sheet
Hi Steve
Try this one on a copy of your workbook Sub test() Dim WSNew As Worksheet Dim Mysheet As Worksheet Set Mysheet = ActiveSheet For Each cell In Range("B2:IV2").SpecialCells(xlConstants) Set WSNew = Worksheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If Mysheet.Columns(cell.Column).Copy WSNew.Range("A1") Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... i did not create the worksheet i am working with. The data is organized so the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
new sheet
this code started creating a bunch of sheets. there's only 3 different
salesman. i need it to go through row 57 and filter out the salesman by name. i should end up with three sheets. thanks for your help ron "Ron de Bruin" wrote: Hi Steve Try this one on a copy of your workbook Sub test() Dim WSNew As Worksheet Dim Mysheet As Worksheet Set Mysheet = ActiveSheet For Each cell In Range("B2:IV2").SpecialCells(xlConstants) Set WSNew = Worksheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If Mysheet.Columns(cell.Column).Copy WSNew.Range("A1") Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... i did not create the worksheet i am working with. The data is organized so the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
new sheet
Hi Steve
Do you have duplicate names in row 2 ? -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... this code started creating a bunch of sheets. there's only 3 different salesman. i need it to go through row 57 and filter out the salesman by name. i should end up with three sheets. thanks for your help ron "Ron de Bruin" wrote: Hi Steve Try this one on a copy of your workbook Sub test() Dim WSNew As Worksheet Dim Mysheet As Worksheet Set Mysheet = ActiveSheet For Each cell In Range("B2:IV2").SpecialCells(xlConstants) Set WSNew = Worksheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If Mysheet.Columns(cell.Column).Copy WSNew.Range("A1") Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... i did not create the worksheet i am working with. The data is organized so the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
new sheet
yes, there are 3 salesmen, each one has about 30 jobs
"Ron de Bruin" wrote: Hi Steve Do you have duplicate names in row 2 ? -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... this code started creating a bunch of sheets. there's only 3 different salesman. i need it to go through row 57 and filter out the salesman by name. i should end up with three sheets. thanks for your help ron "Ron de Bruin" wrote: Hi Steve Try this one on a copy of your workbook Sub test() Dim WSNew As Worksheet Dim Mysheet As Worksheet Set Mysheet = ActiveSheet For Each cell In Range("B2:IV2").SpecialCells(xlConstants) Set WSNew = Worksheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If Mysheet.Columns(cell.Column).Copy WSNew.Range("A1") Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... i did not create the worksheet i am working with. The data is organized so the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
new sheet
Ahhaaaa
I will make a example for you this evening -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... yes, there are 3 salesmen, each one has about 30 jobs "Ron de Bruin" wrote: Hi Steve Do you have duplicate names in row 2 ? -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... this code started creating a bunch of sheets. there's only 3 different salesman. i need it to go through row 57 and filter out the salesman by name. i should end up with three sheets. thanks for your help ron "Ron de Bruin" wrote: Hi Steve Try this one on a copy of your workbook Sub test() Dim WSNew As Worksheet Dim Mysheet As Worksheet Set Mysheet = ActiveSheet For Each cell In Range("B2:IV2").SpecialCells(xlConstants) Set WSNew = Worksheets.Add On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then MsgBox "Change the name of : " & WSNew.Name & " manually" Err.Clear End If Mysheet.Columns(cell.Column).Copy WSNew.Range("A1") Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... i did not create the worksheet i am working with. The data is organized so the headings are by row, not column. For example: A B C 1 Commision % 8.2 9.3 2 Sales Rep dave bill 3 Job # 1 2 I need to go through row 2, and make a sheet for each salesman. but it needs to copy the whole column with it. most sheets are set up so these would be the column headings. i don't want to reorganize the sheet by transposing the data, but something like that could be hidden in the code if need be. i'm stuck. so i'd appreciate any help. THANKS, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
| Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
| How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
| Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming | |||
Hybrid Mode
