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?

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?
>
>

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

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")

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")
>
>