Create New Sheets Based on Specific Values in a Column with VBA

[Tguillaume]

Hi all,

I am trying to use a macro to create multiple new worksheets based on the values in a column of an existing worksheet. There are many posts on this site and others that show me how to create new sheets for each unique value in a column. The trouble that I am having is that I need to specify the specific values for the new sheets I want to create. For example, in the mini-sheet I uploaded I want all rows with "Acute Hospital Utilization" and "Emergency Department Utilization" in column B to be included together in one worksheet and all other rows with any other values in column B to be put in a separate worksheet.

I'm including a link below that shows how to create new sheets for each unique value in a column, in case anyone reading this post would like to see that information:

- VBA - Create new sheet based on cell data

Thanks!
Tguillaume

Sample Data.xlsx
	A	B
1	Clinic	Measure
2	Clinic A	PCP Visit
3	Clinic A	PCP Visit
4	Clinic A	PCP Visit
5	Clinic A	PCP Visit
6	Clinic A	PCP Visit
7	Clinic A	PCP Visit
8	Clinic A	PCP Visit
9	Clinic A	PCP Visit
10	Clinic A	PCP Visit
11	Clinic A	PCP Visit
12	Clinic A	PCP Visit
13	Clinic A	PCP Visit
14	Clinic A	PCP Visit
15	Clinic A	PCP Visit
16	Clinic A	PCP Visit
17	Clinic A	PCP Visit
18	Clinic A	PCP Visit
19	Clinic A	PCP Visit
20	Clinic A	PCP Visit
21	Clinic A	PCP Visit
22	Clinic A	PCP Visit
23	Clinic A	PCP Visit
24	Clinic A	Acute Hospital Utilization
25	Clinic A	Acute Hospital Utilization_M
26	Clinic A	ED Utilization - 18 years and above
27	Clinic A	Emergency Department Utilization
28	Clinic B	PCP Visit
29	Clinic B	PCP Visit
30	Clinic B	PCP Visit
31	Clinic B	PCP Visit
32	Clinic B	PCP Visit
33	Clinic B	PCP Visit
34	Clinic B	PCP Visit
35	Clinic B	PCP Visit
36	Clinic B	PCP Visit
37	Clinic B	PCP Visit
38	Clinic B	PCP Visit
39	Clinic B	PCP Visit
40	Clinic B	PCP Visit
41	Clinic B	PCP Visit
42	Clinic B	PCP Visit
43	Clinic B	PCP Visit
44	Clinic B	PCP Visit
45	Clinic B	PCP Visit
46	Clinic B	PCP Visit
47	Clinic B	PCP Visit
48	Clinic B	PCP Visit
49	Clinic B	PCP Visit
50	Clinic B	PCP Visit
51	Clinic B	PCP Visit
52	Clinic B	PCP Visit
53	Clinic B	PCP Visit
54	Clinic B	PCP Visit
55	Clinic B	PCP Visit
56	Clinic B	PCP Visit
57	Clinic B	PCP Visit
58	Clinic B	PCP Visit
59	Clinic B	PCP Visit
60	Clinic B	PCP Visit
61	Clinic B	PCP Visit
62	Clinic B	PCP Visit
63	Clinic B	PCP Visit
64	Clinic B	PCP Visit
65	Clinic B	PCP Visit
66	Clinic B	PCP Visit
67	Clinic B	PCP Visit
68	Clinic B	PCP Visit
69	Clinic B	PCP Visit
70	Clinic B	PCP Visit
71	Clinic B	PCP Visit
72	Clinic B	PCP Visit
73	Clinic B	PCP Visit
74	Clinic B	PCP Visit
75	Clinic B	PCP Visit
76	Clinic B	PCP Visit
77	Clinic B	PCP Visit
78	Clinic B	PCP Visit
79	Clinic B	PCP Visit
80	Clinic B	PCP Visit
81	Clinic B	PCP Visit
82	Clinic B	PCP Visit
83	Clinic B	PCP Visit
84	Clinic B	PCP Visit
85	Clinic B	PCP Visit
86	Clinic B	PCP Visit
87	Clinic B	PCP Visit
88	Clinic B	PCP Visit
89	Clinic B	PCP Visit
90	Clinic B	PCP Visit
91	Clinic B	PCP Visit
92	Clinic B	PCP Visit
93	Clinic B	PCP Visit
94	Clinic B	PCP Visit
95	Clinic B	PCP Visit
96	Clinic B	PCP Visit
97	Clinic B	PCP Visit
98	Clinic B	PCP Visit
99	Clinic B	PCP Visit
100	Clinic B	PCP Visit
101	Clinic B	PCP Visit
102	Clinic B	PCP Visit
103	Clinic B	PCP Visit
104	Clinic B	PCP Visit
105	Clinic B	PCP Visit
106	Clinic B	PCP Visit
107	Clinic B	PCP Visit
108	Clinic B	PCP Visit
109	Clinic B	PCP Visit
110	Clinic B	PCP Visit
111	Clinic B	PCP Visit
112	Clinic B	PCP Visit
113	Clinic B	PCP Visit
114	Clinic B	PCP Visit
115	Clinic B	PCP Visit
116	Clinic B	PCP Visit
117	Clinic B	PCP Visit
118	Clinic B	PCP Visit
119	Clinic B	PCP Visit
120	Clinic B	PCP Visit
121	Clinic B	PCP Visit
122	Clinic B	PCP Visit
123	Clinic B	PCP Visit
124	Clinic B	PCP Visit
125	Clinic B	PCP Visit
126	Clinic B	PCP Visit
127	Clinic B	PCP Visit
128	Clinic B	PCP Visit
129	Clinic B	PCP Visit
130	Clinic B	PCP Visit
131	Clinic B	Risk of Continued Opioid Use - Age 18 to 64
132	Clinic C	PCP Visit
133	Clinic C	PCP Visit
134	Clinic C	PCP Visit
135	Clinic C	PCP Visit
136	Clinic C	PCP Visit
137	Clinic C	PCP Visit
138	Clinic C	PCP Visit
139	Clinic C	PCP Visit
140	Clinic C	PCP Visit
141	Clinic C	PCP Visit
142	Clinic C	PCP Visit
143	Clinic C	PCP Visit
144	Clinic C	PCP Visit
145	Clinic C	PCP Visit
146	Clinic C	PCP Visit
147	Clinic C	PCP Visit
148	Clinic C	PCP Visit
149	Clinic C	PCP Visit
150	Clinic C	PCP Visit
151	Clinic C	PCP Visit
152	Clinic C	PCP Visit
153	Clinic C	PCP Visit
154	Clinic C	PCP Visit
155	Clinic C	PCP Visit
156	Clinic C	PCP Visit
157	Clinic C	PCP Visit
158	Clinic C	PCP Visit
159	Clinic C	PCP Visit
160	Clinic C	PCP Visit
161	Clinic C	PCP Visit
162	Clinic C	PCP Visit
163	Clinic C	PCP Visit
164	Clinic C	PCP Visit
165	Clinic C	PCP Visit
166	Clinic C	PCP Visit
167	Clinic C	PCP Visit
168	Clinic C	PCP Visit
169	Clinic C	PCP Visit
170	Clinic C	PCP Visit
171	Clinic C	PCP Visit
172	Clinic C	PCP Visit
173	Clinic C	PCP Visit
174	Clinic C	PCP Visit
175	Clinic C	PCP Visit
176	Clinic C	PCP Visit
177	Clinic C	PCP Visit
178	Clinic C	PCP Visit
179	Clinic C	PCP Visit
180	Clinic C	PCP Visit
181	Clinic C	PCP Visit
182	Clinic C	PCP Visit
183	Clinic C	PCP Visit
184	Clinic C	PCP Visit
185	Clinic C	PCP Visit
186	Clinic C	PCP Visit
187	Clinic C	PCP Visit
188	Clinic C	PCP Visit
189	Clinic C	PCP Visit
190	Clinic C	PCP Visit
191	Clinic C	PCP Visit
192	Clinic C	PCP Visit
193	Clinic C	PCP Visit
194	Clinic C	PCP Visit
195	Clinic C	PCP Visit
196	Clinic C	PCP Visit
197	Clinic C	PCP Visit
198	Clinic C	PCP Visit
199	Clinic C	PCP Visit
200	Clinic C	PCP Visit
201	Clinic C	PCP Visit
202	Clinic C	PCP Visit
203	Clinic C	PCP Visit
204	Clinic C	PCP Visit
205	Clinic C	PCP Visit
206	Clinic C	PCP Visit
207	Clinic C	PCP Visit
208	Clinic C	PCP Visit
209	Clinic C	PCP Visit
210	Clinic C	PCP Visit
211	Clinic C	PCP Visit
212	Clinic C	PCP Visit
213	Clinic C	PCP Visit
214	Clinic C	PCP Visit
215	Clinic C	PCP Visit
216	Clinic C	PCP Visit
217	Clinic C	PCP Visit
218	Clinic C	PCP Visit
219	Clinic C	PCP Visit
220	Clinic C	PCP Visit
221	Clinic C	PCP Visit
222	Clinic C	PCP Visit
223	Clinic C	PCP Visit
224	Clinic C	PCP Visit
225	Clinic C	PCP Visit
226	Clinic C	PCP Visit
227	Clinic C	PCP Visit
228	Clinic C	PCP Visit
229	Clinic C	PCP Visit
230	Clinic C	PCP Visit
231	Clinic C	PCP Visit
232	Clinic C	PCP Visit
233	Clinic C	PCP Visit
234	Clinic C	PCP Visit
235	Clinic C	PCP Visit
236	Clinic C	PCP Visit
237	Clinic C	PCP Visit
238	Clinic C	PCP Visit
239	Clinic C	PCP Visit
240	Clinic C	PCP Visit
241	Clinic C	PCP Visit
242	Clinic C	PCP Visit
243	Clinic C	PCP Visit
244	Clinic C	PCP Visit
245	Clinic C	PCP Visit
246	Clinic C	PCP Visit
247	Clinic C	PCP Visit
248	Clinic C	PCP Visit
249	Clinic C	PCP Visit
250	Clinic C	PCP Visit
251	Clinic C	PCP Visit
252	Clinic C	PCP Visit
253	Clinic C	PCP Visit
254	Clinic C	PCP Visit
255	Clinic C	PCP Visit
256	Clinic C	PCP Visit
257	Clinic C	PCP Visit
258	Clinic C	PCP Visit
259	Clinic C	PCP Visit
260	Clinic C	PCP Visit
261	Clinic C	PCP Visit
262	Clinic C	PCP Visit
263	Clinic C	PCP Visit
264	Clinic C	PCP Visit
265	Clinic C	PCP Visit
266	Clinic C	PCP Visit
267	Clinic C	PCP Visit
268	Clinic C	PCP Visit
269	Clinic C	PCP Visit
270	Clinic C	PCP Visit
271	Clinic C	PCP Visit
272	Clinic C	PCP Visit
273	Clinic C	PCP Visit
274	Clinic C	PCP Visit
275	Clinic C	PCP Visit
276	Clinic C	PCP Visit
277	Clinic C	PCP Visit
278	Clinic C	PCP Visit
279	Clinic C	PCP Visit
280	Clinic C	PCP Visit
281	Clinic C	PCP Visit
282	Clinic C	PCP Visit
283	Clinic C	18 years and above
284	Clinic C	18 years and above
285	Clinic E	ED Utilization - 18 years and above
286	Clinic E	Emergency Department Utilization
287	Clinic E	Acute Hospital Utilization
288	Clinic E	Acute Hospital Utilization
289	Clinic E	Acute Hospital Utilization_M
290	Clinic E	Acute Hospital Utilization_S
291	Clinic E	ED Utilization - 18 years and above
292	Clinic E	Emergency Department Utilization
293	Clinic E	18 years and above
294	Clinic E	Controlling High Blood Pressure
295	Clinic E	Blood Pressure Control (<140/90)
296	Clinic E	Controlling High Blood Pressure
297	Clinic E	HbA1C Control (<8%)
298	Clinic E	Retinal Eye Exam
299	Clinic E	ED Utilization - 18 years and above
300	Clinic E	Emergency Department Utilization
301	Clinic E	Colorectal Cancer Screening
302	Clinic E	Colorectal Cancer Screening
303	Clinic E	Controlling High Blood Pressure
304	Clinic E	ED Utilization - 18 years and above
305	Clinic E	Emergency Department Utilization
306	Clinic E	Controlling High Blood Pressure
307	Clinic E	Colorectal Cancer Screening
308	Clinic E	Colorectal Cancer Screening
309	Clinic E	Colorectal Cancer Screening
310	Clinic E	Blood Pressure Control (<140/90)
311	Clinic E	HbA1C Control (<8%)
312	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 65 to 74
313	Clinic E	Controlling High Blood Pressure
314	Clinic E	Blood Pressure Control (<140/90)
315	Clinic E	Controlling High Blood Pressure
316	Clinic E	HbA1C Control (<8%)
317	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 65 to 74
318	Clinic E	Controlling High Blood Pressure
319	Clinic E	Colorectal Cancer Screening
320	Clinic E	HbA1C Control (<8%)
321	Clinic E	Retinal Eye Exam
322	Clinic E	Colorectal Cancer Screening
323	Clinic E	HbA1C Control (<8%)
324	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
325	Clinic E	Risk of Continued Opioid Use - Age 18 to 64
326	Clinic E	ED Utilization - 18 years and above
327	Clinic E	ED Utilization - 18 years and above
328	Clinic E	Emergency Department Utilization
329	Clinic E	Emergency Department Utilization
330	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
331	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
332	Clinic E	Retinal Eye Exam
333	Clinic E	Controlling High Blood Pressure
334	Clinic E	Colorectal Cancer Screening
335	Clinic E	Colorectal Cancer Screening
336	Clinic E	Use of Opioids from Multiple Providers
337	Clinic E	Colorectal Cancer Screening
338	Clinic E	Controlling High Blood Pressure
339	Clinic E	Colorectal Cancer Screening
340	Clinic E	Controlling High Blood Pressure
341	Clinic E	Colorectal Cancer Screening
342	Clinic E	ED Utilization - 18 years and above
343	Clinic E	Emergency Department Utilization
344	Clinic E	Controlling High Blood Pressure
345	Clinic E	Blood Pressure Control (<140/90)
346	Clinic E	Colorectal Cancer Screening
347	Clinic E	Controlling High Blood Pressure
348	Clinic E	HbA1C Control (<8%)
349	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
350	Clinic E	Retinal Eye Exam
351	Clinic E	Use of Imaging Studies for Low Back Pain
352	Clinic E	HbA1C Control (<8%)
353	Clinic E	Retinal Eye Exam
354	Clinic E	ED Utilization - 18 years and above
355	Clinic E	Emergency Department Utilization
356	Clinic E	Colorectal Cancer Screening
357	Clinic E	Blood Pressure Control (<140/90)
358	Clinic E	Colorectal Cancer Screening
359	Clinic E	Controlling High Blood Pressure
360	Clinic E	HbA1C Control (<8%)
361	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
362	Clinic E	HbA1C Control (<8%)
363	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 65 to 74
364	Clinic E	Retinal Eye Exam
365	Clinic E	Controlling High Blood Pressure
366	Clinic E	Colorectal Cancer Screening
367	Clinic E	Controlling High Blood Pressure
368	Clinic E	Colorectal Cancer Screening
369	Clinic E	Controlling High Blood Pressure
370	Clinic E	Colorectal Cancer Screening
371	Clinic E	Controlling High Blood Pressure
372	Clinic E	Acute Hospital Utilization
373	Clinic E	Acute Hospital Utilization_S
374	Clinic E	Colorectal Cancer Screening
375	Clinic E	Controlling High Blood Pressure
376	Clinic E	Risk of Continued Opioid Use - Age 18 to 64
377	Clinic E	Colorectal Cancer Screening
378	Clinic E	Controlling High Blood Pressure
379	Clinic E	Blood Pressure Control (<140/90)
380	Clinic E	Colorectal Cancer Screening
381	Clinic E	Controlling High Blood Pressure
382	Clinic E	Blood Pressure Control (<140/90)
383	Clinic E	Colorectal Cancer Screening
384	Clinic E	Controlling High Blood Pressure
385	Clinic E	Kidney Health Evaluation for Patients With Diabetes - Age 18 to 64
386	Clinic E	Blood Pressure Control (<140/90)
387	Clinic E	Colorectal Cancer Screening
388	Clinic E	Controlling High Blood Pressure
389	Clinic E	ED Utilization - 18 years and above
390	Clinic E	Emergency Department Utilization
391	Clinic E	Colorectal Cancer Screening
392	Clinic E	Controlling High Blood Pressure
393	Clinic E	ED Utilization - 18 years and above
394	Clinic E	ED Utilization - 18 years and above
395	Clinic E	Emergency Department Utilization
396	Clinic E	Emergency Department Utilization
397	Clinic E	Controlling High Blood Pressure
398	Clinic E	Controlling High Blood Pressure
399	Clinic E	Controlling High Blood Pressure
400	Clinic E	ED Utilization - 18 years and above
Sheet1

 

[Marc L]

Hi !​

I want all rows with "Acute Hospital Utilization" and "Emergency Department Utilization" in column B to be included together in one worksheet and all other rows with any other values in column B to be put in a separate worksheet.

Easy with a filter or better with an advanced filter but as you forgot to mention in which worksheets …​

​

 

[Tguillaume]

Hi !​

Easy with a filter or better with an advanced filter but as you forgot to mention in which worksheets …​

​

Hi Marc!

Thanks so much for responding - I was actually able to figure it out by recording a macro, filtering on values in a column, and then reading the resulting code. I am posting what I used below in case anyone else has this issue! In the code below, you would have to specify your range (mine was A:I) and the field you want to filter on (in my case it was field 6), as well as the workbook name, sheet name, and the criteria.

wbWorkbookName.Worksheets("Worksheet_Name").Range("A:I").AutoFilter Field:=6, Criteria1:=Array("Criteria 1", _
                                                                                      "Criteria2", _
                                                                                      "Criteria3"), _
                                                                                    
             Operator:=xlFilterValues