Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Variable Range of Data into Different Worksheets
I have a single worksheet with example data as shown:
A1 B1 C1 Name Job Manager John S. Super Bill Sue M. Analyst Bill Jack V. Clerk Bill Gary W. Sr Analyst Lisa Bob N. Clerk Lisa Tim B. Super Phyllis Z. Nate M. Clerk Phyllis Z. John Q. Clerk Phyllis Z. Quin L. Analyst Phyllis Z. Paul S. Analyst Phyllis Z. Each manager has a varying number of employees. I need to create a new worksheet for each manager (in the same workbook) containing all the manager's employee's (and their Job). Thus the final product will be a workbook with 4 worksheets: 1 with original data, 3 containing each manager's employees. How would I do this using VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Variable Range of Data into Different Worksheets
Hi,
Try this: Sub GetManagerLists() Dim ws1 As Worksheet, ws2 As Worksheet Dim lastrow As Long, r As Long, n As Long Dim manager As String Set ws1 = Worksheets("sheet1") ws1.Activate With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set myRange = .Range("c2:c" & lastrow) r = 2 Do While r < lastrow manager = .Cells(r, "C") Sheets.Add after:=Sheets(Worksheets.Count) ActiveSheet.Name = manager Set ws2 = Worksheets(manager) n = Application.CountIf(myRange, manager) .Cells(r, 1).Resize(n, 2).Copy ws2.Cells(2, 1) r = r + n Loop End With End Sub HTH "equity7252" wrote: I have a single worksheet with example data as shown: A1 B1 C1 Name Job Manager John S. Super Bill Sue M. Analyst Bill Jack V. Clerk Bill Gary W. Sr Analyst Lisa Bob N. Clerk Lisa Tim B. Super Phyllis Z. Nate M. Clerk Phyllis Z. John Q. Clerk Phyllis Z. Quin L. Analyst Phyllis Z. Paul S. Analyst Phyllis Z. Each manager has a varying number of employees. I need to create a new worksheet for each manager (in the same workbook) containing all the manager's employee's (and their Job). Thus the final product will be a workbook with 4 worksheets: 1 with original data, 3 containing each manager's employees. How would I do this using VBA? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Variable Range of Data into Different Worksheets
I think I'd try to keep all my data on one sheet. Then use
Data|filter|Autofilter to see names. But if you want to split the data from one worksheet into many worksheets based on a column, then both Debra Dalgleish and Ron de Bruin may have solutions for you: Debra's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb or Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb And Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm equity7252 wrote: I have a single worksheet with example data as shown: A1 B1 C1 Name Job Manager John S. Super Bill Sue M. Analyst Bill Jack V. Clerk Bill Gary W. Sr Analyst Lisa Bob N. Clerk Lisa Tim B. Super Phyllis Z. Nate M. Clerk Phyllis Z. John Q. Clerk Phyllis Z. Quin L. Analyst Phyllis Z. Paul S. Analyst Phyllis Z. Each manager has a varying number of employees. I need to create a new worksheet for each manager (in the same workbook) containing all the manager's employee's (and their Job). Thus the final product will be a workbook with 4 worksheets: 1 with original data, 3 containing each manager's employees. How would I do this using VBA? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Variable Range of Data into Different Worksheets
Toppers...
Thanks for the reply. However, I received a "Run-time error '9': Script out of range" error message at the following code: Set ws1 = Worksheets("sheet1") |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separate Variable Range of Data into Different Worksheets
Hi,
"Sheet1" is the sheet which contains your original list. I suspect you havre called it something different so change "Sheet1" to the tab name of your sheet. I should have explained this my original posting - my apologies. HTH "equity7252" wrote: Toppers... Thanks for the reply. However, I received a "Run-time error '9': Script out of range" error message at the following code: Set ws1 = Worksheets("sheet1") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate raw data onto different worksheets | Excel Worksheet Functions | |||
Show Data In Range not appearing in Separate Range | Excel Discussion (Misc queries) | |||
Applying range names to existing formulas in separate worksheets | Excel Discussion (Misc queries) | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
Compare data on 2 separate worksheets. | Excel Worksheet Functions |