Create Pivot Table By Tab

[freddyboots]

Hi.

Via VBA code, I would like to create a pivot table by tab for each change in Rep name. So in the example attached there would be 3 tabs created using classic Pivot tables from the Raw Data. The ADB field should be subtotaled by Dealer Code as listed in the second attachment.


RAW DATA

Rep ADB.xlsx
	A	B	C	D	E	F	G
1	Rep Name	Dealer Code	Dealer Name	Branch Code	Fund Code	Fund Name	ADB
2	Johnson	000123	ABC Corp	2YR	9998	Fund Name 1	1,000,000.00
3	Johnson	000123	ABC Corp	3YR	9997	Fund Name 16	2,000,000.00
4	Johnson	000123	ABC Corp	3YZ	9997	Fund Name 16	(500,000.00)
5	Johnson	000124	DEF Corp	DIV	9989	Fund Name 2	3,000,000.00
6	Johnson	000125	GHI Corp	00000004	8222	Fund Name 3	4,000,000.00
7	Johnson	000126	JKL Corp	4603	1217	Fund Name 4	5,000,000.00
8	Jones	000123	ABC Corp	2YR	9998	Fund Name 1	100,000.00
9	Jones	000123	ABC Corp	3YR	9997	Fund Name 16	200,000.00
10	Jones	000124	DEF Corp	DIV	9989	Fund Name 2	300,000.00
11	Jones	000125	GHI Corp	00000004	8222	Fund Name 3	400,000.00
12	Jones	000126	JKL Corp	4603	1217	Fund Name 4	500,000.00
13	Smith	000123	ABC Corp	2YR	9998	Fund Name 1	10,000.00
14	Smith	000123	ABC Corp	3YR	9997	Fund Name 16	20,000.00
15	Smith	000124	DEF Corp	DIV	9989	Fund Name 2	30,000.00
16	Smith	000125	GHI Corp	00000004	8222	Fund Name 3	40,000.00
17	Smith	000126	JKL Corp	4603	1217	Fund Name 4	50,000.00
Data

EXPECTED RESULTS OF ONE PIVOT TABLE

Rep ADB.xlsx
	A	B	C	D	E	F	G
1
2
3	Sum of ADB
4	Rep Name	Dealer Code	Dealer Name	Branch Code	Fund Code	Fund Name	Total
5	Johnson	000123	ABC Corp	2YR	9998	Fund Name 1	1,000,000.00
6				3YR	9997	Fund Name 16	2,000,000.00
7				3YZ	9997	Fund Name 16	(500,000.00)
8		000123 Total					2,500,000.00
9		000124	DEF Corp	DIV	9989	Fund Name 2	3,000,000.00
10		000124 Total					3,000,000.00
11		000125	GHI Corp	00000004	8222	Fund Name 3	4,000,000.00
12		000125 Total					4,000,000.00
13		000126	JKL Corp	4603	1217	Fund Name 4	5,000,000.00
14		000126 Total					5,000,000.00
15	Johnson Total						14,500,000.00
16	Grand Total						14,500,000.00
Johnson

 

[Crystalyzer]

Why not create one pivot table with a slicer for Rep Name?

 

[Alex Blakenburg]

Also you do know that pivot tables can be exploded to do exactly what you want right ?
The only difference is that the name will be in the Page Filter header instead of in the Pivot table body.

Steps:

• Pull Rep Name into the filter section
• PivotTable Analyze > Pivot Table (far left) > Options > Show Report Pages

If you want to convert it into a macro just record it when you do it.

Warning: Each pivot table will have "all" the data sitting under it. So don't give it to someone as a pivot table thinking they will be restricted to just seeing their own data.
You would need to convert the pivot to being just values if you want to restrict their viewing access.

 

[freddyboots]

Why not create one pivot table with a slicer for Rep Name?

Thank you for the reply. I didn't realize this is an option, I need it broken out by tab.