Subsetting a worksheet with a macro and/or VBA.

[Bob Fraser]

Subset (divide) the first worksheet (Sheet1) into separate sheets by a variable(District)
Copy each to its own new worksheet and name the worksheet for the variable ("One", "Two", etc.).
The rows per District and the number of Districts will vary.
Background: Excel 2000
Product is to be Counted by School and Cost is Summed by School -
so manually this is done with two Pivot Tables which are Copy Pasted by District
to have Count and Sum on one row per Product.

Thanks, Bob Fraser




District School Teacher Product Cost
One Al Bill Ec 120
One Al **** Ec 120
One Al Joe Ec 120
One Al John Ec 120
One Al Sam Bu 60
One Al Sam Ec 120
One Be Art Ho 70
One Be Art Mo 300
One Be Ken Pl 80
One Ca Ann Ec 120
One Ca Ann Mn 100
Two Co Jill Ec 120
Two Co Jill Mn 100
Two Co Jill Pl 80
Two De Amy Bu 60
Two De Amy Mo 300
Two De Fran Ec 120
Two De Sue Ec 120
Two De Sue Ho 70
Three Ao Jed Bu 60
Three Ao Bob Bu 60
Three Gu Liz Ec 120
Three Gu LIz Ho 70
Three Gu Sal Bu 60
Three Mt Moe Ec 120
Three Mt Moe Mn 100
Three Mt Jim Bu 60
Three To Ray Ec 120
Three To Ray Ho 70
Three To Les Bu 60

 

[Bob Fraser]

Hi again,
Works great; thanks. Is there any way to get multi "Data Items" on one row, rather than in column?

 

[tusharm]

Drag the label marked 'Data' so that it overlaps the 'Total' label and let go.

 

[Bob Fraser]

Hi poorwallace,
What should be the first row in the first wks showas up as the last. Don't worry about it. I'm sure I can sort it out when I get a chance to trace it.
4" to 7' snow in Boston tomorrow.
Bob