Subsetting a worksheet with a macro and/or VBA.

Bob Fraser

New Member
Joined
Dec 24, 2005
Messages
7
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

New Member
Joined
Dec 24, 2005
Messages
7
Hi again,
Works great; thanks. Is there any way to get multi "Data Items" on one row, rather than in column?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Drag the label marked 'Data' so that it overlaps the 'Total' label and let go.
 

Bob Fraser

New Member
Joined
Dec 24, 2005
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top