Split excel Data into separate Workbook rather then tabs.

[nihad]

Hi All

I have built a template for our budget holder management accounts. We currently have 137 budgets to circulate(!) so converting this into 137 files is not realistic under a manual process.

Basically, I am after a macro that will do the following

Create a Summary file and transaction list for each of Sage Mapping Code mentioned in Sage Mapping Mapping Tab, with those coded to pull all the information from Transaction List based on Column D

MKF Example.xlsx
	A	B	C	D	E
1
2		FY2023 Budget Report
3
4		Subject/Department	Boarders Activities
5		Nominal Code	6238
6
7		Budget Holder	Alex
8		Annual Budget	£11,000
9
10		Month	Monthly Expenditure	Total Expenditure To Date	Available
11		Sep-22	0	0	11,000
12		Oct-22	528	528	10,472
13		Nov-22	1,427	1,955	9,045
14		Dec-22	2,065	4,020	6,980
15		Jan-23	0	4,020	6,980
16		Feb-23	0	4,020	6,980
17		Mar-23	0	4,020	6,980
18		Apr-23	0	4,020	6,980
19		May-23	0	4,020	6,980
20		Jun-23	0	4,020	6,980
21		Jul-23	0	4,020	6,980
22		Aug-23	0	4,020	6,980
23		Total	4,020
24
Summary

Cell Formulas
Range	Formula
C4	C4	=VLOOKUP($C$5,'Sage Mapping'!$A:$D,3,FALSE)
C7	C7	=VLOOKUP($C$5,'Sage Mapping'!$A:$D,4,FALSE)
C8	C8	=-VLOOKUP($C$5,Budget!$A:$G,7,FALSE)
D11	D11	=C11
E11:E22	E11	=$C$8-D11
D12:D22	D12	=D11+C12
C11:C22	C11	=SUMIFS('Transaction List'!$J:$J,'Transaction List'!$D:$D,Summary!$C$5,'Transaction List'!$M:$M,Summary!$B11)
C23	C23	=SUM(C11:C22)

Named Ranges
Name	Refers To	Cells
Budget!_FilterDatabase	=Budget!$A$1:$G$193	C8
'Sage Mapping'!_FilterDatabase	='Sage Mapping'!$A$1:$G$171	C4, C7

MKF Example.xlsx
	D	E	F	G	H	I	J
1	NominalRecord.AccountReference	NominalRecord.AccountName	TransactionNominal.Number	TransactionNominal.Type	TransactionNominal.Date	TransactionNominal.Details	TransactionNominal.Amount
2	6280	Vote - History - DRB	447033	PI	26/09/2022	Histyr KS3 Rev Ind Emp	230.40
3	6280	Vote - History - DRB	448985	PI	09/11/2022	History Bomb membership	150.00
4	6280	Vote - History - DRB	449470	PI	28/10/2022	History tofay sub	70.00
5	6280	Vote - History - DRB	449471	PI	28/10/2022	South Africa in the 20th century	6.71
6	6280	Vote - History - DRB	449508	PI	28/10/2022	Themes of the American Civil War	31.35
7	6280	Vote - History - DRB	449541	PI	28/10/2022	Wordwall	64.80
8	6280	Vote - History - DRB	450603	PI	28/11/2022	Kahoot! ASA	115.53
9	6285	Vote - Cricket	449510	PI	28/10/2022	Cricket Bowling Machine	434.98
10	6285	Vote - Cricket	449514	PI	28/10/2022	Armball thrower	19.99
11	6290	Vote - Science - SB	446647	PI	08/09/2022	Chems	30.90
12	6290	Vote - Science - SB	446648	PI	15/09/2022	Sub 1 Nov - 31 Oct 2023	50.00
13	6290	Vote - Science - SB	447359	PI	01/09/2022	Science items	45.96
14	6290	Vote - Science - SB	447508	PI	28/09/2022	Samples	89.95
15	6290	Vote - Science - SB	447659	PI	14/10/2022	CLEAPSS Membership	190.69
16	6290	Vote - Science - SB	448238	PI	28/10/2022	Chems	13.67
17	6290	Vote - Science - SB	449518	PI	28/10/2022	Hardback manuscript books	35.90
18	6290	Vote - Science - SB	450024	PI	01/12/2022	Nuffield Operational Amplifier	45.48
19	6290	Vote - Science - SB	450257	PI	15/11/2022	Escherichia Coli K12 train	23.94
20	6290	Vote - Science - SB	450605	PI	28/11/2022	Electrophoris mains transformer & Bacterial Transformation k	48.40
21	6290	Vote - Science - SB	450641	PI	28/11/2022	Childrens scissors	21.99
22	6290	Vote - Science - SB	450671	PI	28/11/2022	A4 manuscript ruled books	48.13
23	6290	Vote - Science - SB	450679	PI	28/11/2022	6 X 5 Lamb hearts	57.95
24	6300	Vote - Library -	447852	PI	28/09/2022	The Economist digital - annual	208.00
25	6305	Vote - School Productions - PC	449484	PI	28/10/2022	Neck & Wrist shackles	14.95
26	6305	Vote - School Productions - PC	449488	PI	28/10/2022	GCSE Drama Monologues & Duologues	30.98
27	6305	Vote - School Productions - PC	449490	PI	28/10/2022	Halloween shackles	16.99
28	6305	Vote - School Productions - PC	449506	PI	28/10/2022	Lanterns	57.30
29	6305	Vote - School Productions - PC	449530	PI	28/10/2022	Chauffeur accessories etc	18.99
30	6305	Vote - School Productions - PC	450344	PI	09/12/2022	Flowers for after play	45.00
31	6305	Vote - School Productions - PC	450624	PI	28/11/2022	Cat O'Nines whip	8.94
32	6305	Vote - School Productions - PC	450633	PI	28/11/2022	Ebay - play wardrobe items	396.00
33	6305	Vote - School Productions - PC	450651	PI	28/11/2022	Gaffer tape	20.82
34	6305	Vote - School Productions - PC	450652	PI	28/11/2022	Polyester satin	28.83
35	6305	Vote - School Productions - PC	450653	PI	28/11/2022	Cotton Canvas	100.44
36	6305	Vote - School Productions - PC	450654	PI	28/11/2022	Polyester satin	28.83
37	6305	Vote - School Productions - PC	450655	PI	28/11/2022	Carpet underlay	300.00
38	6305	Vote - School Productions - PC	450661	PI	28/11/2022	White frilly shirt & tricorn	24.98
39	6305	Vote - School Productions - PC	450662	PI	28/11/2022	Feather pen	9.59
40	6305	Vote - School Productions - PC	450663	PI	28/11/2022	Steampunk pirate jacket	26.49
41	6305	Vote - School Productions - PC	450664	PI	28/11/2022	Jute rope	6.79
42	6305	Vote - School Productions - PC	450665	PI	28/11/2022	4 cravat & cuff sets	40.04
43	6305	Vote - School Productions - PC	450666	PI	28/11/2022	Handkerchiefs	5.94
44	6305	Vote - School Productions - PC	450667	PI	28/11/2022	Medieval costume shirts	63.98
45	6305	Vote - School Productions - PC	450668	PI	28/11/2022	Telescope	39.99
46	6305	Vote - School Productions - PC	450669	PI	28/11/2022	2 x tricorn hatd	21.48
47	6305	Vote - School Productions - PC	450674	PI	28/11/2022	Hessian roll	115.09
48	6305	Vote - School Productions - PC	450707	PI	28/11/2022	Acrylic paints	77.54
49	6305	Vote - School Productions - PC	450723	PI	28/11/2022	Hessian Roll tarpaulins	115.09
50	6310	Vote - Music - JJ	448018	SC	24/10/2022	Music Lessons (Flute) - Summer term - Ella TANNER	263.25
51	6310	Vote - Music - JJ	448055	PI	19/10/2022	Tickets to 10 Nov Fantastic Fairytales	37.00
52	6310	Vote - Music - JJ	449057	PI	27/10/2022	Hire Studio x 2 hours	16.00
53	6310	Vote - Music - JJ	450043	PI	23/11/2022	14th Oct Coach TP said put to music for now	245.00
54	6310	Vote - Music - JJ	450289	PI	23/11/2022	Catering College	17.50
55	6310	Vote - Music - JJ	450836	PI	14/12/2022	College string ensemble 9th Dec 2022	80.00
56	6310	Vote - Music - JJ	451074	SI	21/12/2022	For performance at Landulph Festival by Mount Kelly Chamber	(300.00)
57	6315	Vote - Athletics -	447602	PI	12/10/2022	Affiliation Fee to Aug 2023	140.00
58	6319	Vote - Football Girls	446640	PI	15/09/2022	Year 3 2022-2023	13,750.00
59	6319	Vote - Football Girls	447973	CP	14/09/2022	Referee fee for girl's football match - 14/9/22	25.00
60	6319	Vote - Football Girls	448237	PI	24/10/2022	Goalkeeper coaching	262.50
61	6319	Vote - Football Girls	449743	CP	09/11/2022	Referees Fees - 9/11/2022	100.00
62	6319	Vote - Football Girls	450838	PI	13/12/2022	Goalkeeping coaching	157.50
63	6320	Vote - Match Teas	449155	PI	26/10/2022	Catering Oct 2022	791.60
64	6320	Vote - Match Teas	450297	PI	23/11/2022	Catering College	771.90
65	6321	Vote - Football	449515	PI	28/10/2022	Training Bibs	54.99
66	6325	Vote - Hockey -	446646	PI	13/09/2022	Facemasks	152.16
67	6325	Vote - Hockey -	448988	PI	08/11/2022	Hockey balls	93.51
68	6325	Vote - Hockey -	449464	PI	28/10/2022	Yahoo arm guards & OBO Wheelie Bag	374.99
69	6325	Vote - Hockey -	449512	PI	28/10/2022	Football bibs	50.95
70	6325	Vote - Hockey -	449524	PI	28/10/2022	All Terrain combi padlock	77.84
71	6325	Vote - Hockey -	450685	PI	28/11/2022	OBO OGO Kickers	165.00
72	6325	Vote - Hockey -	450913	PI	16/12/2022	Hockey Balls	186.00
73	6330	Vote - Market Garden	450710	PI	28/11/2022	Bulbs and seeds	53.94
74	6335	Vote - Rugby - PN	446085	PI	02/09/2022	Rugby Balls	720.00
75	6335	Vote - Rugby - PN	447497	PI	29/09/2022	Clothing samples	105.60
76	6335	Vote - Rugby - PN	447900	PI	28/09/2022	Rugby shirts	116.38
77	6335	Vote - Rugby - PN	448019	PI	13/10/2022	Rugby Ties	699.78
78	6335	Vote - Rugby - PN	448962	PI	17/09/2022	Engraving cups	8.00
79	6335	Vote - Rugby - PN	449803	PI	24/11/2022	Refs rugby fixtures Sept and 5th Oct	105.00
80	6335	Vote - Rugby - PN	450406	JD	13/12/2022	Transfer to Belfast Rugby Tour	500.00
81	6335	Vote - Rugby - PN	450413	PI	13/12/2022	Entry fee Rugby 7's	15.00
Transaction List

MKF Example.xlsx
	A	B
1	N/C	Budget Holder
2	7052	Jessica
3	8555	Jessica
4	7550	Jessica
5	7140	Jessica
6	7777	Linda
7	7858	Mat
8	7100	Jessica
9	8590	Adam
10	7110	Jessica
11	8635	Chris
12	8850	Jessica
13	6519	Jessica
14	8625	Vanessa
15	7854	Linda
16	7870	Linda
17	6769	Katy
18	6319	Tommy
19	8550	Jessica
20	6520	Jessica
21	7059	Jessica
22	6426	Adam
23	7590	Jessica
24	7856	Linda
25	6360	Emma
26	7570	Jessica
27	7080	Helen
28	6203	Adam
29	7850	Jessica
30	8610	Vanessa
31	7902	Jessica
32	6482	Adam
33	6755	Jessica
34	6525	Jessica
35	8626	Vanessa
36	6455	Jessica
37	7090	Helen
38	7120	Jessica
39	7871	Linda
40	8580	Jessica
41	6445	Adam
42	6201	James
43	8570	Jessica
44	8560	Chris
45	6521	Jessica
46	7873	Mat
47	6751	Dom
48	8595	Jessica
49	7877	Linda
50	6238	Alex
51	8585	Jessica
52	8600	Jessica
53	6753	Adam
54	7060	Jessica
55	8865	Chris
56	6391	Katy
57	7070	Hannah
58	7560	Helen
59	8100	Jessica
60	7876	Jessica
61	8630	Jessica
62	7580	Jessica
63	7065	Jessica
64	8620	Nicola
65	8904	Jessica
66	6441	James
67	6530	James
68	6335	Bobby
69	8557	Jessica
70	8622	Jessica
71	7559	Jessica
72	8565	Jessica
73	6756	Nick
74	6305	Lizzie
75	6320	Adam
76	6534	Alan
77	6400	Becky
78	6781	Dom
79	7901	Jessica
80	8628	Fran
81	6325	Bobby
82	6752	Dom
83	6222	Lou
84	6750	Dom
85	6524	Jessica
86	8572	Nicola
87	6532	Bobby
88	6270	Luke
89	6764	Jo
90	7859	Linda
91	6245	Adam
92	6240	Netty
93	6290	Sarah
94	7712	Jessica
95	6255	Becky
96	6770	Michelle
97	6280	Drew
98	6205	James
99	6440	Guy
100	6260	Belinda
101	7075	Hannah
102	6215	Alex
103	6390	Katy
104	6774	Nick
105	6766	Andrew
106	6285	Bobby
107	7555	Jessica
108	6224	Drew
109	6775	Dom
110	6761	David
111	6310	Adam
112	6765	Liz
113	6230	Adam
114	6759	Piers
115	6772	Maria
116	6433	Katy
117	6767	Carolynn
118	6300	James
119	6531	Drew
120	6345	Bobby
121	6442	Adam
122	8860	Chris
123	6763	Dom
124	6427	Jessica
125	6434	Lizzie
126	6315	Bobby
127	7874	Nick
128	6435	Vanessa
129	6540	James
130	7053	Jessica
131	6370	Maria
132	6760	Matty
133	6321	Bobby
134	6330	Adam
135	7095	Helen
136	6522	Jessica
137	6200	Maria
138	6523	Jessica
139	6000	Staff
140	6005	Staff
141	6010	Staff
142	6050	Staff
143	6100	Staff
144	6210	Victoria
145	6600	Staff
146	6605	Staff
147	6610	Staff
148	6650	Staff
149	6700	Staff
150	7000	Staff
151	7002	Staff
152	7005	Staff
153	7010	Staff
154	7500	Staff
155	7505	Staff
156	7510	Staff
157	7700	Staff
158	7705	Staff
159	7710	Staff
160	7878	Staff
161	7879	Staff
162	7882	Staff
163	8500	Staff
164	8502	Staff
165	8505	Staff
166	8510	Staff
167	8800	Staff
168	8802	Staff
169	8805	Staff
170	8810	Staff
171	6444	Adam
Sage Mapping

 

[James006]

Hi,

Your request, as you can easily imagine ..., is fairly frequent ...

Split Each Excel Sheet Into Separate Files (Step-by-Step)

Learn how to quickly split each Excel sheet into separate files/PDFs. Use a simple VBA code to quickly split sheets into separate files and save it.

trumpexcel.com

 

[nihad]

my date isn't on separate tab
it's all on same tab