Create new Table(s) with specific data from existing table

[kapvg]

Hi,

I want to create new tables (1 for each day of the week) from an existing table.
The existing table has data in the below format.

Dated	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
7/1/2021	Thursday	0.69	0.72	0.75	1.01	0.82	1.11	0.71	0.91	0.62	1.44	0.72	0.84
7/2/2021	Friday	0.69	0.74	0.76	1.01	0.82	1.16	0.71	0.91	0.64	1.46	0.72	0.86
7/6/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
7/7/2021	Wednesday	0.74	0.85	0.84	1.11	0.93	1.26	0.77	0.96	0.74	1.66	0.83	0.99
7/8/2021	Thursday	0.76	0.85	0.87	1.13	0.94	1.32	0.85	1.01	0.74	1.69	0.86	1.14
7/9/2021	Friday	0.76	0.85	0.89	1.18	0.95	1.35	0.85	1.02	0.75	1.69	0.86	1.14
7/10/2021	Saturday	0.76	0.85	0.89	1.17	0.95	1.36	0.85	1.01	0.74	1.69	0.86	1.12
7/11/2021	Sunday	0.76	0.84	0.9	1.18	0.94	1.35	0.85	1.01	0.75	1.67	0.85	1.12
7/12/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
7/13/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
7/14/2021	Wednesday	0.77	0.86	0.92	1.19	0.98	1.38	0.88	1.04	0.77	1.7	0.89	1.13
7/15/2021	Thursday	0.78	0.86	0.94	1.19	0.97	1.4	0.89	1.05	0.77	1.69	0.9	1.14
7/16/2021	Friday	0.79	0.86	0.95	1.22	0.96	1.42	0.89	1.05	0.78	1.71	0.9	1.14
7/17/2021	Saturday	0.78	0.86	0.94	1.21	0.98	1.41	0.89	1.05	0.78	1.71	0.9	1.13
7/18/2021	Sunday	0.42	0.55	0.57	0.79	0.54	0.79	0.47	0.56	0.37	0.77	0.46	0.53
7/19/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
7/20/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
7/21/2021	Wednesday	0.62	0.8	0.72	1.01	0.75	1	0.69	0.81	0.58	1.34	0.66	0.76
7/22/2021	Thursday	0.61	0.81	0.72	1.05	0.78	1.01	0.7	0.82	0.59	1.34	0.69	0.78
7/23/2021	Friday	0.61	0.84	0.72	1.06	0.79	1.01	0.71	0.83	0.6	1.35	0.71	0.79
7/24/2021	Saturday	0.62	0.84	0.72	1.08	0.79	1	0.7	0.83	0.61	1.35	0.72	0.8
7/25/2021	Sunday	0.62	0.84	0.73	1.09	0.79	1.01	0.69	0.83	0.61	1.35	0.72	0.8
7/26/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
7/27/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
7/28/2021	Wednesday	0.67	0.89	0.77	1.12	0.85	1.03	0.76	0.92	0.66	1.42	0.77	0.87

There are no formulae or anything else in the data table.
Any suggestion on how I can use VBA to create 7 new tables (1 for each day of the week) with the corresponding data?

Thanks!!
~kg

 

[RasGhul]

Here's a formula solution if this suits, note that I converted your data to an excel table, so each table will update automatically;

Just ensure there is enough rows for the Filter function to spill the corresponding data;

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Dated	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
2	1/07/2021	Thursday	0.69	0.72	0.75	1.01	0.82	1.11	0.71	0.91	0.62	1.44	0.72	0.84
3	2/07/2021	Friday	0.69	0.74	0.76	1.01	0.82	1.16	0.71	0.91	0.64	1.46	0.72	0.86
4	6/07/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
5	7/07/2021	Wednesday	0.74	0.85	0.84	1.11	0.93	1.26	0.77	0.96	0.74	1.66	0.83	0.99
6	8/07/2021	Thursday	0.76	0.85	0.87	1.13	0.94	1.32	0.85	1.01	0.74	1.69	0.86	1.14
7	9/07/2021	Friday	0.76	0.85	0.89	1.18	0.95	1.35	0.85	1.02	0.75	1.69	0.86	1.14
8	10/07/2021	Saturday	0.76	0.85	0.89	1.17	0.95	1.36	0.85	1.01	0.74	1.69	0.86	1.12
9	11/07/2021	Sunday	0.76	0.84	0.9	1.18	0.94	1.35	0.85	1.01	0.75	1.67	0.85	1.12
10	12/07/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
11	13/07/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
12	14/07/2021	Wednesday	0.77	0.86	0.92	1.19	0.98	1.38	0.88	1.04	0.77	1.7	0.89	1.13
13	15/07/2021	Thursday	0.78	0.86	0.94	1.19	0.97	1.4	0.89	1.05	0.77	1.69	0.9	1.14
14	16/07/2021	Friday	0.79	0.86	0.95	1.22	0.96	1.42	0.89	1.05	0.78	1.71	0.9	1.14
15	17/07/2021	Saturday	0.78	0.86	0.94	1.21	0.98	1.41	0.89	1.05	0.78	1.71	0.9	1.13
16	18/07/2021	Sunday	0.42	0.55	0.57	0.79	0.54	0.79	0.47	0.56	0.37	0.77	0.46	0.53
17	19/07/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
18	20/07/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
19	21/07/2021	Wednesday	0.62	0.8	0.72	1.01	0.75	1	0.69	0.81	0.58	1.34	0.66	0.76
20	22/07/2021	Thursday	0.61	0.81	0.72	1.05	0.78	1.01	0.7	0.82	0.59	1.34	0.69	0.78
21	23/07/2021	Friday	0.61	0.84	0.72	1.06	0.79	1.01	0.71	0.83	0.6	1.35	0.71	0.79
22	24/07/2021	Saturday	0.62	0.84	0.72	1.08	0.79	1	0.7	0.83	0.61	1.35	0.72	0.8
23	25/07/2021	Sunday	0.62	0.84	0.73	1.09	0.79	1.01	0.69	0.83	0.61	1.35	0.72	0.8
24	26/07/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
25	27/07/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
26	28/07/2021	Wednesday	0.67	0.89	0.77	1.12	0.85	1.03	0.76	0.92	0.66	1.42	0.77	0.87
27
28	Monday	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
29	12/07/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
30	19/07/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
31	26/07/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
32
33	Tuesday	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
34	6/07/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
35	13/07/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
36	20/07/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
37	27/07/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
Sheet1

Cell Formulas
Range	Formula
A29:N31	A29	=FILTER(Table1[#Data],Table1[Day]=$A$28,"")
A34:N37	A34	=FILTER(Table1[#Data],Table1[Day]=$A$33,"")
Dynamic array formulas.

 

[kapvg]

Here's a formula solution if this suits, note that I converted your data to an excel table, so each table will update automatically;

Just ensure there is enough rows for the Filter function to spill the corresponding data;

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Dated	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
2	1/07/2021	Thursday	0.69	0.72	0.75	1.01	0.82	1.11	0.71	0.91	0.62	1.44	0.72	0.84
3	2/07/2021	Friday	0.69	0.74	0.76	1.01	0.82	1.16	0.71	0.91	0.64	1.46	0.72	0.86
4	6/07/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
5	7/07/2021	Wednesday	0.74	0.85	0.84	1.11	0.93	1.26	0.77	0.96	0.74	1.66	0.83	0.99
6	8/07/2021	Thursday	0.76	0.85	0.87	1.13	0.94	1.32	0.85	1.01	0.74	1.69	0.86	1.14
7	9/07/2021	Friday	0.76	0.85	0.89	1.18	0.95	1.35	0.85	1.02	0.75	1.69	0.86	1.14
8	10/07/2021	Saturday	0.76	0.85	0.89	1.17	0.95	1.36	0.85	1.01	0.74	1.69	0.86	1.12
9	11/07/2021	Sunday	0.76	0.84	0.9	1.18	0.94	1.35	0.85	1.01	0.75	1.67	0.85	1.12
10	12/07/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
11	13/07/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
12	14/07/2021	Wednesday	0.77	0.86	0.92	1.19	0.98	1.38	0.88	1.04	0.77	1.7	0.89	1.13
13	15/07/2021	Thursday	0.78	0.86	0.94	1.19	0.97	1.4	0.89	1.05	0.77	1.69	0.9	1.14
14	16/07/2021	Friday	0.79	0.86	0.95	1.22	0.96	1.42	0.89	1.05	0.78	1.71	0.9	1.14
15	17/07/2021	Saturday	0.78	0.86	0.94	1.21	0.98	1.41	0.89	1.05	0.78	1.71	0.9	1.13
16	18/07/2021	Sunday	0.42	0.55	0.57	0.79	0.54	0.79	0.47	0.56	0.37	0.77	0.46	0.53
17	19/07/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
18	20/07/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
19	21/07/2021	Wednesday	0.62	0.8	0.72	1.01	0.75	1	0.69	0.81	0.58	1.34	0.66	0.76
20	22/07/2021	Thursday	0.61	0.81	0.72	1.05	0.78	1.01	0.7	0.82	0.59	1.34	0.69	0.78
21	23/07/2021	Friday	0.61	0.84	0.72	1.06	0.79	1.01	0.71	0.83	0.6	1.35	0.71	0.79
22	24/07/2021	Saturday	0.62	0.84	0.72	1.08	0.79	1	0.7	0.83	0.61	1.35	0.72	0.8
23	25/07/2021	Sunday	0.62	0.84	0.73	1.09	0.79	1.01	0.69	0.83	0.61	1.35	0.72	0.8
24	26/07/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
25	27/07/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
26	28/07/2021	Wednesday	0.67	0.89	0.77	1.12	0.85	1.03	0.76	0.92	0.66	1.42	0.77	0.87
27
28	Monday	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
29	12/07/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
30	19/07/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
31	26/07/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
32
33	Tuesday	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
34	6/07/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
35	13/07/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
36	20/07/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
37	27/07/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
Sheet1

Cell Formulas
Range	Formula
A29:N31	A29	=FILTER(Table1[#Data],Table1[Day]=$A$28,"")
A34:N37	A34	=FILTER(Table1[#Data],Table1[Day]=$A$33,"")
Dynamic array formulas.

Thanks for this but I am looking at a VBA solution primarily coz the main data table is also something that I am creating via a VBA and thus it will increase in size overtime.

I was able to find VBA code snippets for copying the data in a Table via VBA but am unable to figure out how to create new tables only with data for specific days of the week. Any help would be great!!!

thanks!!

 

[RasGhul]

My VBA knowledge is limited, maybe this. I added this to a shape so that once a day has been selected (A29) I can run the macro;

Sub RectangleRoundedCorners1_Click()

Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Sheet1").Range("A31").CurrentRegion

Application.ScreenUpdating = False

    rg.Offset(1).ClearContents

Dim rgdata As Range, rgcriteria As Range, rgOutput As Range

    Set rgdata = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
        Set rgcriteria = ThisWorkbook.Worksheets("Sheet1").Range("A28").CurrentRegion
    Set rgOutput = ThisWorkbook.Worksheets("Sheet1").Range("A31").CurrentRegion

    rgdata.AdvancedFilter xlFilterCopy, rgcriteria, rgOutput

Application.ScreenUpdating = True

End Sub

Create new Tables with specific data from existing table_kapvg.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Dated	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
2	1/07/2021	Thursday	0.69	0.72	0.75	1.01	0.82	1.11	0.71	0.91	0.62	1.44	0.72	0.84
3	2/07/2021	Friday	0.69	0.74	0.76	1.01	0.82	1.16	0.71	0.91	0.64	1.46	0.72	0.86
4	6/07/2021	Tuesday	0.73	0.81	0.82	1.09	0.91	1.23	0.76	0.94	0.74	1.66	0.81	0.97
5	7/07/2021	Wednesday	0.74	0.85	0.84	1.11	0.93	1.26	0.77	0.96	0.74	1.66	0.83	0.99
6	8/07/2021	Thursday	0.76	0.85	0.87	1.13	0.94	1.32	0.85	1.01	0.74	1.69	0.86	1.14
7	9/07/2021	Friday	0.76	0.85	0.89	1.18	0.95	1.35	0.85	1.02	0.75	1.69	0.86	1.14
8	10/07/2021	Saturday	0.76	0.85	0.89	1.17	0.95	1.36	0.85	1.01	0.74	1.69	0.86	1.12
9	11/07/2021	Sunday	0.76	0.84	0.9	1.18	0.94	1.35	0.85	1.01	0.75	1.67	0.85	1.12
10	12/07/2021	Monday	0.76	0.86	0.9	1.18	0.95	1.36	0.85	1.02	0.76	1.7	0.87	1.14
11	13/07/2021	Tuesday	0.77	0.86	0.92	1.18	0.97	1.36	0.86	1.03	0.76	1.7	0.88	1.14
12	14/07/2021	Wednesday	0.77	0.86	0.92	1.19	0.98	1.38	0.88	1.04	0.77	1.7	0.89	1.13
13	15/07/2021	Thursday	0.78	0.86	0.94	1.19	0.97	1.4	0.89	1.05	0.77	1.69	0.9	1.14
14	16/07/2021	Friday	0.79	0.86	0.95	1.22	0.96	1.42	0.89	1.05	0.78	1.71	0.9	1.14
15	17/07/2021	Saturday	0.78	0.86	0.94	1.21	0.98	1.41	0.89	1.05	0.78	1.71	0.9	1.13
16	18/07/2021	Sunday	0.42	0.55	0.57	0.79	0.54	0.79	0.47	0.56	0.37	0.77	0.46	0.53
17	19/07/2021	Monday	0.57	0.77	0.7	0.95	0.72	0.94	0.65	0.77	0.57	1.26	0.62	0.65
18	20/07/2021	Tuesday	0.58	0.79	0.72	0.99	0.74	0.96	0.67	0.77	0.57	1.27	0.63	0.68
19	21/07/2021	Wednesday	0.62	0.8	0.72	1.01	0.75	1	0.69	0.81	0.58	1.34	0.66	0.76
20	22/07/2021	Thursday	0.61	0.81	0.72	1.05	0.78	1.01	0.7	0.82	0.59	1.34	0.69	0.78
21	23/07/2021	Friday	0.61	0.84	0.72	1.06	0.79	1.01	0.71	0.83	0.6	1.35	0.71	0.79
22	24/07/2021	Saturday	0.62	0.84	0.72	1.08	0.79	1	0.7	0.83	0.61	1.35	0.72	0.8
23	25/07/2021	Sunday	0.62	0.84	0.73	1.09	0.79	1.01	0.69	0.83	0.61	1.35	0.72	0.8
24	26/07/2021	Monday	0.62	0.85	0.73	1.09	0.8	1.01	0.7	0.84	0.62	1.37	0.73	0.82
25	27/07/2021	Tuesday	0.63	0.86	0.74	1.09	0.8	1.02	0.72	0.86	0.62	1.38	0.73	0.85
26	28/07/2021	Wednesday	0.67	0.89	0.77	1.12	0.85	1.03	0.76	0.92	0.66	1.42	0.77	0.87
27
28	Day
29	Wednesday
30
31	Dated	Day	B1	B2	B3	B4	B5	B6	B7	B8	B9	B10	B11	B12
32	7/07/2021	Wednesday	0.74	0.85	0.84	1.11	0.93	1.26	0.77	0.96	0.74	1.66	0.83	0.99
33	14/07/2021	Wednesday	0.77	0.86	0.92	1.19	0.98	1.38	0.88	1.04	0.77	1.7	0.89	1.13
34	21/07/2021	Wednesday	0.62	0.8	0.72	1.01	0.75	1	0.69	0.81	0.58	1.34	0.66	0.76
35	28/07/2021	Wednesday	0.67	0.89	0.77	1.12	0.85	1.03	0.76	0.92	0.66	1.42	0.77	0.87
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A29	List	=$B$9:$B$15