Excel Macro how do I filter time using VBA to copy/paste to different sheet

[navydog]

I'm trying to use VBA to automate a process I perform multiple times a day. I've figured out a little bit but I've got 2 different columns containing time. The estimated time is sometimes populated so I want to be able to filter on 2 conditions: either ET (Estimated Time) is populated and contains a time in a range (between 06:30 and 14:30) and if empty, then Scheduled Time falls between 06:30 and 14:30. Once that filtering is done, then copy/paste those resulting rows into a different sheet. I know almost nothing about VBA and need help...PLEASE!!

 

[John_w]

I think two separate filters (auto filter or advanced filter) would be needed for this. The first filters ET >= 06:30 and <=14:30 and copies the visible rows to the results sheet. The second filters ET = blank and Scheduled Time >= 06:30 and <= 14:30 and copies the visible rows to the results sheet, below the first results. However, the result rows would not be in the same order as the source data rows. If you need to keep them in the same order you would need a column in the source data which could be sorted in the results to restore the correct order. Do you need this and is there such a column?

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Should be able to do it with a single Advanced Filter & keeping the relative row order.

I am unsure just where your data starts and ends but this might give you something to work on.
I have assumed

• Data actually starts in column A & on 'Sheet1'
• Nothing is to the right of this data table. My code uses the column 2 to the right of the data (col H in my sample Sheet1 below) as a helper column for the Advanced Filter.
• Results are to go to 'Sheet2' which exists but has no data.

Test with a copy of your workbook.

Sub Test()
  Dim rCrit As Range
 
  With Sheets("Sheet1").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(6,30,0),EndT,TIME(14,30,0),OR(AND(E2>=StartT,E2<=EndT),AND(E2="""",D2>=StartT,D2<=EndT)))"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With
End Sub

My sample data

navydog.xlsm
	A	B	C	D	E	F	G	H
1	Hdr 1	Arrival/Departure	Routing	Scheduled Time	ET	Hdr 6
2	A2	Departure	ABE	9:55		F2
3	A3	Departure	ABE	14:18		F3
4	A4	Departure	ABE	21:12	21:40	F4
5	A5	Departure	ABQ	8:50		F5
6	A6	Departure	ABQ	20:55	22:09	F6
7	A7	Departure	ABQABQ	14:19		F7
8	A8	Departure	ABY	12:17		F8
9	A9	Departure	ABY	19:54	20:24	F9
10	A10	Departure	AEX	14:50		F10
11	A11	Departure	AEX	19:54	22:05	F11
12	A12	Departure	AGS	8:25	8:40	F12
13	A13	Departure	AGS	14:15		F13
14	A14	Departure	AGS	18:05		F14
15
Sheet1

Result of the above code.

navydog.xlsm
	A	B	C	D	E	F	G
1	Hdr 1	Arrival/Departure	Routing	Scheduled Time	ET	Hdr 6
2	A2	Departure	ABE	9:55		F2
3	A3	Departure	ABE	14:18		F3
4	A5	Departure	ABQ	8:50		F5
5	A7	Departure	ABQABQ	14:19		F7
6	A8	Departure	ABY	12:17		F8
7	A12	Departure	AGS	8:25	8:40	F12
8	A13	Departure	AGS	14:15		F13
9
Sheet2

 

[John_w]

Very nice, Peter!

 

[Peter_SSs]

Thanks John.

 

[navydog]

Thank you John and Peter. I have taken your sample code, Peter, and modified it for my specific data set and it's working like I need it...MOSTLY. I'm parsing through the data for 3 different shifts, 6:30-14:30, 15:00-2300, 2300-06:30. The first 2 shifts are working as expected and are returning correct results. The final shift, though, is returning all rows from the original sheet. I'll provide the code for the first 2 shifts as well as the 3rd shift so that you can see if there's anything glaringly obvious that I'm missing. I'm so close yet so far away!! For the 3rd shift, I had to create 2 sets of time variables because it's not working to wrap the end of the 1st day with the beginning of the 2nd day (23:00-23:59 and 00:00-06:30). Any ideas??

Sub Test()
  Dim rCrit As Range
 
  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(6,30,0),EndT,TIME(15,00,0),OR(AND(F2>=StartT,G2<=EndT,F2>=StartT,F2<=EndT),AND(G2="""",F2>=StartT,F2<=EndT),AND(F2<=StartT,G2>=StartT,G2<=EndT),AND(F2>EndT,G2>=StartT,G2<=EndT)))"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("AM").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(15,01,0),EndT,TIME(23,00,0),OR(AND(F2>=StartT,G2<=EndT,F2>=StartT,F2<=EndT),AND(G2="""",F2>=StartT,F2<=EndT),AND(F2<=StartT,G2>=StartT,G2<=EndT),AND(F2>EndT,G2>=StartT,G2<=EndT)))"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("PM").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(23,01,0),EndT,TIME(23,59,0),StartT2,TIME(00,00,0),EndT2,TIME(06,29,0),OR(AND(F2<=StartT, G2>=StartT, G2<=EndT, G2<>""""), AND(F2>=StartT, F2<=EndT, G2>=StartT, G2<=EndT, G2<>""""),AND(F2>=StartT, F2<=EndT, G2=""""),AND(F2>=EndT2, G2>=StartT2, G2<=EndT2), AND(F2>=StartT2, F2<=EndT2, G2>=StartT2, G2<=EndT2, G2<>""""), AND(F2>=EndT2, G2>=StartT2, G2<=EndT2, G2<>"""")))"
   .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("RON").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

End Sub

 

[navydog]

ok belay my last. I figured out my issue with the filter criteria and now it's returning the correct rows and exporting to new sheet. Now, I'm wanting to do a simple count of rows that meet another search criteria for each of the 3 newly created sheets. Is there an easy way to do that count and then use the values from those counts to perform calculations? For instance, I want to do a count of the number of rows containing a specific value and then divide that count by the total number of rows to get a percentage of rows that contain that specific value.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I figured out my issue with the filter criteria and now it's returning the correct rows and exporting to new sheet.

Glad you figured out that the problem with that last shift was that the start time was later than the finish time & therefore needed a modified approach.

However, I have some doubts about your posted code. To be sure though, and make any constructive comments, I would need to see some revised sample data and revised requirement since the layout and conditions to transfer to another sheet appear to have changed.

Now, I'm wanting to do a simple count of rows that meet another search criteria for each of the 3 newly created sheets. Is there an easy way to do that count and then use the values from those counts to perform calculations? For instance, I want to do a count of the number of rows containing a specific value and then divide that count by the total number of rows to get a percentage of rows that contain that specific value.

For us to understand, could you please provide some sample data and the expected results from that sample data (with XL2BB as requested earlier) and explain again in relation to that sample/results?

 

[navydog]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.



Glad you figured out that the problem with that last shift was that the start time was later than the finish time & therefore needed a modified approach.

However, I have some doubts about your posted code. To be sure though, and make any constructive comments, I would need to see some revised sample data and revised requirement since the layout and conditions to transfer to another sheet appear to have changed.



For us to understand, could you please provide some sample data and the expected results from that sample data (with XL2BB as requested earlier) and explain again in relation to that sample/results?

Here is the Xl2bb code for the sample data

LAVEXPORT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Flight Number	Regstrn	A/C Type	Routing	Scheduled Date	Scheduled Time	ET	Gate	Svc	Svc Time	Prio	LPrio	Service
2	903				7/28/2023	21:15	8:10		yes	04:38 (07/30/23)	Normal	Critical
3	4164				7/28/2023	21:41	13:35		yes	04:38 (07/30/23)	Normal	Normal
4	772				7/28/2023	22:49	10:00		yes	04:38 (07/30/23)	Normal	Normal
5	317				7/29/2023	7:04			yes	04:38 (07/30/23)	Normal	Critical
6	1228				7/29/2023	7:05	7:10		yes	04:38 (07/30/23)	Normal	Normal
7	2634				7/29/2023	7:05	7:30		yes	04:38 (07/30/23)	Normal	Normal
8	2069				7/29/2023	7:05			yes	04:38 (07/30/23)	Normal	Normal
9	2390				7/29/2023	7:05			yes	04:38 (07/30/23)	Normal	Normal
10	327				7/29/2023	7:05			yes	04:38 (07/30/23)	Normal	Critical
11	839				7/29/2023	7:05			yes	04:38 (07/30/23)	Normal	Critical
12	1343				7/29/2023	7:10	7:20		yes	04:38 (07/30/23)	Normal	Critical
13	2523				7/29/2023	7:10			yes	04:38 (07/30/23)	Normal	Normal
14	2896				7/29/2023	7:14			yes	04:38 (07/30/23)	Normal	Normal
15	1330				7/29/2023	7:15			yes	04:38 (07/30/23)	Normal	Normal
16	409				7/29/2023	7:15			yes	04:38 (07/30/23)	Normal	Critical
17	2526				7/29/2023	7:16			yes	04:38 (07/30/23)	Normal	Normal
18	1416				7/29/2023	7:20			yes	04:38 (07/30/23)	Normal	Normal
19	1610				7/29/2023	7:20			yes	04:38 (07/30/23)	Normal	Critical
20	1646				7/29/2023	7:20			yes	04:38 (07/30/23)	Normal	Critical
21	2014				7/29/2023	7:28			yes	04:39 (07/30/23)	Normal	Critical
22	2332				7/29/2023	7:30			yes	04:39 (07/30/23)	Normal	Normal
23	2328				7/29/2023	7:40			yes	04:40 (07/30/23)	Normal	Normal
24	2039				7/29/2023	7:44			yes	04:38 (07/30/23)	Normal	Normal
25	1311				7/29/2023	7:45	8:55		yes	04:38 (07/30/23)	Normal	Normal
26	2656				7/29/2023	7:56			yes	04:38 (07/30/23)	Normal	Normal
27	2651				7/29/2023	7:58			yes	04:38 (07/30/23)	Normal	Normal
28	1676				7/29/2023	8:00			yes	04:38 (07/30/23)	Normal	Critical
29	1285				7/29/2023	8:04			yes	04:39 (07/30/23)	Normal	Normal
30	1509				7/29/2023	8:10	8:25		yes	04:39 (07/30/23)	Normal	Normal
31	898				7/29/2023	8:10	8:45		yes	04:38 (07/30/23)	Normal	Critical
32	2107				7/29/2023	8:10			yes	04:38 (07/30/23)	Normal	Normal
33	2470				7/29/2023	8:10			yes	04:39 (07/30/23)	Normal	Normal
34	733				7/29/2023	8:10			yes	04:38 (07/30/23)	Normal	Critical
35	849				7/29/2023	8:10			yes	04:39 (07/30/23)	Normal	Critical
36	916				7/29/2023	8:10			yes	04:38 (07/30/23)	Normal	Critical
37	2991				7/29/2023	8:12			yes	04:39 (07/30/23)	Normal	Normal
38	965				7/29/2023	8:15	8:21		yes	04:40 (07/30/23)	Normal	Critical
39	830				7/29/2023	8:15	8:35		yes	04:38 (07/30/23)	Normal	Critical
40	2763				7/29/2023	8:15	9:55		yes	04:39 (07/30/23)	Normal	Normal
41	1847				7/29/2023	8:15			yes	04:38 (07/30/23)	Normal	Critical
42	2161				7/29/2023	8:15			yes	04:38 (07/30/23)	Normal	Normal
43	2223				7/29/2023	8:15			yes	04:39 (07/30/23)	Normal	Normal
44	2301				7/29/2023	8:15			yes	04:38 (07/30/23)	Normal	Normal
45	426				7/29/2023	8:15			yes	04:38 (07/30/23)	Normal	Critical
46	4985				7/29/2023	8:15			yes	04:39 (07/30/23)	Normal	Normal
47	781				7/29/2023	8:15			yes	04:39 (07/30/23)	Normal	Normal
48	418				7/29/2023	8:16			yes	04:38 (07/30/23)	Normal	Critical
49	2519				7/29/2023	8:20	8:25		yes	04:39 (07/30/23)	Normal	Normal
50	777				7/29/2023	8:20	8:40		yes	04:38 (07/30/23)	Normal	Critical
51	1183				7/29/2023	8:20	8:50		yes	04:38 (07/30/23)	Normal	Critical
52	1089				7/29/2023	8:20			yes	04:38 (07/30/23)	Normal	Normal
53	1213				7/29/2023	8:20			yes	04:38 (07/30/23)	Normal	Critical
54	1760				7/29/2023	8:20			yes	04:38 (07/30/23)	Normal	Critical
55	2336				7/29/2023	8:20			yes	04:39 (07/30/23)	Normal	Normal
56	2868				7/29/2023	8:20			yes	04:39 (07/30/23)	Normal	Normal
57	5484				7/29/2023	8:20			yes	04:39 (07/30/23)	Normal	Normal
58	577				7/29/2023	8:24			yes	04:39 (07/30/23)	Normal	Critical
59	1892				7/29/2023	8:25			yes	04:38 (07/30/23)	Normal	Normal
60	2100				7/29/2023	8:25			yes	04:39 (07/30/23)	Normal	Normal
61	2344				7/29/2023	8:25			yes	04:39 (07/30/23)	Normal	Normal
62	5179				7/29/2023	8:25			yes	04:40 (07/30/23)	Normal	Normal
63	5342				7/29/2023	8:25			yes	04:40 (07/30/23)	Normal	Normal
64	5478				7/29/2023	8:25			yes	04:39 (07/30/23)	Normal	Normal
65	1546				7/29/2023	8:30	8:30		yes	04:39 (07/30/23)	Normal	Normal
66	725				7/29/2023	8:30	8:50		yes	04:39 (07/30/23)	Normal	Critical
67	346				7/29/2023	8:30	9:05		yes	04:39 (07/30/23)	Normal	Critical
68	1924				7/29/2023	8:30			yes	04:38 (07/30/23)	Normal	Normal
69	2817				7/29/2023	8:30			yes	04:39 (07/30/23)	Normal	Normal
70	569				7/29/2023	8:30			yes	04:38 (07/30/23)	Normal	Critical
71	1227				7/29/2023	8:32	8:44		yes	04:38 (07/30/23)	Normal	Normal
72	1134				7/29/2023	8:35			yes	04:39 (07/30/23)	Normal	Normal
73	1728				7/29/2023	8:35			yes	04:39 (07/30/23)	Normal	Normal
74	2357				7/29/2023	8:35			yes	04:39 (07/30/23)	Normal	Normal
75	2632				7/29/2023	8:35			yes	04:39 (07/30/23)	Normal	Critical
76	432				7/29/2023	8:35			yes	04:38 (07/30/23)	Normal	Normal
77	961				7/29/2023	8:35			yes	04:38 (07/30/23)	Normal	Critical
78	2006				7/29/2023	8:36	9:35		yes	04:39 (07/30/23)	Normal	Critical
79	2475				7/29/2023	8:38	8:38		yes	04:38 (07/30/23)	Normal	Critical
80	1964				7/29/2023	8:40	9:00		yes	04:38 (07/30/23)	Normal	Critical
81	1298				7/29/2023	8:40			yes	04:38 (07/30/23)	Normal	Critical
82	2507				7/29/2023	8:41			yes	04:39 (07/30/23)	Normal	Normal
83	786				7/29/2023	8:44			yes	04:38 (07/30/23)	Normal	Critical
84	7037				7/29/2023	8:45			yes	04:40 (07/30/23)	Normal	Critical
85	1709				7/29/2023	8:50	8:55		yes	04:39 (07/30/23)	Normal	Normal
86	1354				7/29/2023	8:50	9:20		yes	04:39 (07/30/23)	Normal	Critical
87	2952				7/29/2023	8:50			yes	04:39 (07/30/23)	Normal	Normal
88	1375				7/29/2023	8:55	9:32		yes	04:39 (07/30/23)	Normal	Critical
89	2035				7/29/2023	8:55	10:55		yes	04:40 (07/30/23)	Normal	Critical
90	1927				7/29/2023	8:55			yes	04:39 (07/30/23)	Normal	Normal
91	2326				7/29/2023	8:55			yes	04:39 (07/30/23)	Normal	Normal
92	2644				7/29/2023	8:55			yes	04:38 (07/30/23)	Normal	Normal
93	9908				7/29/2023	9:00	9:20		yes	04:39 (07/30/23)	Normal	Critical
94	2408				7/29/2023	9:00	9:52		yes	04:39 (07/30/23)	Normal	Critical
95	2169				7/29/2023	9:00			yes	04:39 (07/30/23)	Normal	Critical
96	5291				7/29/2023	9:00			yes	04:40 (07/30/23)	Normal	Normal
97	9900				7/29/2023	9:00					Normal	Critical
98	5172				7/29/2023	9:04	9:35		yes	04:40 (07/30/23)	Normal	Normal
99	2270				7/29/2023	9:08			yes	04:39 (07/30/23)	Normal	Normal
100	2467				7/29/2023	9:09			yes	04:39 (07/30/23)	Normal	Normal
101	1366				7/29/2023	9:10	9:30		yes	04:39 (07/30/23)	Normal	Normal
102	1510				7/29/2023	9:10			yes	04:38 (07/30/23)	Normal	Normal
103	2517				7/29/2023	9:10			yes	04:39 (07/30/23)	Normal	Normal
104	3949				7/29/2023	9:10			yes	04:39 (07/30/23)	Normal	Normal
105	439				7/29/2023	9:10			yes	04:39 (07/30/23)	Normal	Normal
106	5004				7/29/2023	9:10			yes	04:39 (07/30/23)	Normal	Normal
107	5457				7/29/2023	9:10			yes	04:40 (07/30/23)	Normal	Normal
108	920				7/29/2023	9:10			yes	04:39 (07/30/23)	Normal	Normal
109	648				7/29/2023	9:12			yes	04:38 (07/30/23)	Normal	Normal
110	904				7/29/2023	9:15	9:25		yes	04:39 (07/30/23)	Normal	Critical
111	1017				7/29/2023	9:15	9:28		yes	04:38 (07/30/23)	Normal	Normal
112	2983				7/29/2023	9:15			yes	04:39 (07/30/23)	Normal	Normal
113	2207				7/29/2023	9:18			yes	04:39 (07/30/23)	Normal	Normal
114	5460				7/29/2023	9:18			yes	04:40 (07/30/23)	Normal	Normal
115	2682				7/29/2023	9:20	9:40		yes	04:40 (07/30/23)	Normal	Normal
116	2543				7/29/2023	9:20	9:45		yes	04:40 (07/30/23)	Normal	Critical
117	1621				7/29/2023	9:20			yes	04:39 (07/30/23)	Normal	Normal
118	2073				7/29/2023	9:20			yes	04:40 (07/30/23)	Normal	Critical
119	2547				7/29/2023	9:24			yes	04:39 (07/30/23)	Normal	Normal
120	2384				7/29/2023	9:26			yes	04:40 (07/30/23)	Normal	Normal
121	1658				7/29/2023	9:30	10:35		yes	04:39 (07/30/23)	Normal	Critical
122	822				7/29/2023	9:40	9:45		yes	04:39 (07/30/23)	Normal	Critical
123	1316				7/29/2023	9:40	9:47		yes	04:39 (07/30/23)	Normal	Critical
124	501				7/29/2023	9:40	9:50		yes	04:38 (07/30/23)	Normal	Critical
125	1782				7/29/2023	9:40	10:09		yes	04:39 (07/30/23)	Normal	Critical
126	1527				7/29/2023	9:40			yes	04:39 (07/30/23)	Normal	Normal
127	2077				7/29/2023	9:40			yes	04:39 (07/30/23)	Normal	Critical
128	807				7/29/2023	9:42	9:42		yes	04:39 (07/30/23)	Normal	Critical
129	1950				7/29/2023	9:43	9:50		yes	04:39 (07/30/23)	Normal	Critical
130	1898				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Critical
131	1937				7/29/2023	9:45			yes	04:38 (07/30/23)	Normal	Critical
132	1944				7/29/2023	9:45			yes	04:40 (07/30/23)	Normal	Critical
133	1970				7/29/2023	9:45			yes	04:40 (07/30/23)	Normal	Critical
134	1977				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Critical
135	2949				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Normal
136	586				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Critical
137	588				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Critical
138	855				7/29/2023	9:45			yes	04:39 (07/30/23)	Normal	Critical
139	1815				7/29/2023	9:46	10:45		yes	04:39 (07/30/23)	Normal	Critical
140	2755				7/29/2023	9:50	9:50		yes	04:39 (07/30/23)	Normal	Normal
141	1641				7/29/2023	9:50	10:00		yes	04:39 (07/30/23)	Normal	Normal
142	2739				7/29/2023	9:50	10:05		yes	04:39 (07/30/23)	Normal	Normal
143	774				7/29/2023	9:50	10:35		yes	04:38 (07/30/23)	Normal	Critical
144	1061				7/29/2023	9:50			yes	04:39 (07/30/23)	Normal	Normal
145	1796				7/29/2023	9:50			yes	04:40 (07/30/23)	Normal	Critical
146	2182				7/29/2023	9:50			yes	04:40 (07/30/23)	Normal	Normal
147	2400				7/29/2023	9:50			yes	04:39 (07/30/23)	Normal	Normal
148	5341				7/29/2023	9:50			yes	04:40 (07/30/23)	Normal	Normal
149	1809				7/29/2023	9:51	10:11		yes	04:39 (07/30/23)	Normal	Critical
150	2556				7/29/2023	9:54			yes	04:40 (07/30/23)	Normal	Normal
151	1013				7/29/2023	9:55	10:00		yes	04:40 (07/30/23)	Normal	Normal
152	1894				7/29/2023	9:55	10:15		yes	04:39 (07/30/23)	Normal	Critical
153	600				7/29/2023	9:55	12:45		yes	04:40 (07/30/23)	Normal	Normal
154	1432				7/29/2023	9:55			yes	04:39 (07/30/23)	Normal	Critical
155	1765				7/29/2023	9:55			yes	04:40 (07/30/23)	Normal	Critical
156	1882				7/29/2023	9:55			yes	04:39 (07/30/23)	Normal	Critical
157	1986				7/29/2023	9:55			yes	04:39 (07/30/23)	Normal	Critical
158	5193				7/29/2023	9:55			yes	04:40 (07/30/23)	Normal	Normal
159	1992				7/29/2023	9:56	10:45		yes	04:39 (07/30/23)	Normal	Critical
160	2699				7/29/2023	9:58			yes	04:39 (07/30/23)	Normal	Critical
161	1997				7/29/2023	9:59			yes	04:39 (07/30/23)	Normal	Critical
162	787				7/29/2023	10:00	10:13		yes	04:40 (07/30/23)	Normal	Critical
163	1563				7/29/2023	10:00	10:20		yes	04:39 (07/30/23)	Normal	Normal
164	1759				7/29/2023	10:00	10:35		yes	04:40 (07/30/23)	Normal	Critical
165	1929				7/29/2023	10:00	11:01		yes	04:39 (07/30/23)	Normal	Critical
166	1418				7/29/2023	10:00			yes	04:40 (07/30/23)	Normal	Critical
167	4906				7/29/2023	10:00			yes	04:40 (07/30/23)	Normal	Normal
168	752				7/29/2023	10:00			yes	04:39 (07/30/23)	Normal	Critical
169	874				7/29/2023	10:00			yes	04:39 (07/30/23)	Normal	Critical
170	2286				7/29/2023	10:04	10:40		yes	04:40 (07/30/23)	Normal	Critical
171	2147				7/29/2023	10:05	10:25		yes	04:39 (07/30/23)	Normal	Normal
172	2371				7/29/2023	10:05	10:25		yes	04:39 (07/30/23)	Normal	Critical
173	2775				7/29/2023	10:05	10:30		yes	04:39 (07/30/23)	Normal	Critical
174	2017				7/29/2023	10:05	10:46		yes	04:40 (07/30/23)	Normal	Normal
175	1771				7/29/2023	10:05	11:00		yes	04:39 (07/30/23)	Normal	Critical
176	2445				7/29/2023	10:05			yes	04:39 (07/30/23)	Normal	Normal
177	2830				7/29/2023	10:05			yes	04:39 (07/30/23)	Normal	Normal
178	5292				7/29/2023	10:05			yes	04:39 (07/30/23)	Normal	Normal
179	820				7/29/2023	10:05			yes	04:40 (07/30/23)	Normal	Normal
180	582				7/29/2023	10:06	10:28		yes	04:40 (07/30/23)	Normal	Critical
181	1869				7/29/2023	10:09	10:34		yes	04:39 (07/30/23)	Normal	Critical
182	2199				7/29/2023	10:10	10:21		yes	04:40 (07/30/23)	Normal	Normal
183	5262				7/29/2023	10:10	10:31		yes	04:40 (07/30/23)	Normal	Normal
184	2465				7/29/2023	10:10	10:39		yes	04:39 (07/30/23)	Normal	Critical
185	2200				7/29/2023	10:10			yes	04:39 (07/30/23)	Normal	Normal
186	2700				7/29/2023	10:10			no		Normal	Normal
187	2820				7/29/2023	10:10			yes	04:39 (07/30/23)	Normal	Normal
188	2908				7/29/2023	10:10			yes	04:39 (07/30/23)	Normal	Normal
189	533				7/29/2023	10:12			yes	04:39 (07/30/23)	Normal	Normal
190	323				7/29/2023	10:15	10:25		yes	04:39 (07/30/23)	Normal	Critical
191	1660				7/29/2023	10:15	10:40		no		Normal	Normal
192	5438				7/29/2023	10:15	12:00		yes	04:40 (07/30/23)	Normal	Normal
193	2398				7/29/2023	10:15			yes	04:40 (07/30/23)	Normal	Normal
194	324				7/29/2023	10:15			yes	04:39 (07/30/23)	Normal	Critical
195	945				7/29/2023	10:15			yes	04:38 (07/30/23)	Normal	Critical
196	995				7/29/2023	10:15			yes	04:38 (07/30/23)	Normal	Critical
197	1958				7/29/2023	10:16	11:45		yes	04:40 (07/30/23)	Normal	Critical
198	1983				7/29/2023	10:18			yes	04:39 (07/30/23)	Normal	Critical
199	2177				7/29/2023	10:20	10:50		yes	04:39 (07/30/23)	Normal	Normal
200	1153				7/29/2023	10:20			yes	04:39 (07/30/23)	Normal	Normal
Sheet4

 

[navydog]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.



Glad you figured out that the problem with that last shift was that the start time was later than the finish time & therefore needed a modified approach.

However, I have some doubts about your posted code. To be sure though, and make any constructive comments, I would need to see some revised sample data and revised requirement since the layout and conditions to transfer to another sheet appear to have changed.



For us to understand, could you please provide some sample data and the expected results from that sample data (with XL2BB as requested earlier) and explain again in relation to that sample/results?

Here is the current code that I have split into 3 different modules...

Public Sub TextToCol1()

Dim ws As Worksheet, lRow As Long

Set ws = ActiveWorkbook.Worksheets("LAVEXPORT")

ws.Range("B2:C3000").ClearContents

'ws.Range("A1").End(xlUp).TextToColumns , Semicolon:=True
  With ws
    lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A1:A" & lRow).TextToColumns Semicolon:=True
    '.Range("A2:A" & lRow).TextToColumns ConsecutiveDelimiter:=True, Semicolon:=True, Space:=True 'Option 1
    '.Range("A2:B" & lRow).Value = Application.Trim(.Range("A2:B" & lRow)) 'Option 2
    '.Cells(1, 2) = "KPI"
    .Range("A1:N3000").AutoFilter
    .Range("A1:N3000").VerticalAlignment = xlCenter
    .Range("A1:N3000").HorizontalAlignment = xlCenter
    '.Columns("A:N").AutoFit
    .Range("A1:N3000").Columns.AutoFit
        
End With
End Sub



Public Sub SortingColumnsInRange()
    ' Selecting range
    Range("A:N").Select

    ' Sort column C, after column N and after column P
    Selection.Columns.Sort key1:=Columns("E"), Order1:=xlAscending, Key2:=Columns("F"), Order2:=xlAscending, Header:=xlYes
    'Sheets.Add.Name = "AM"
    'Sheets.Add = "PM"
    'Sheets.Add After:=Worksheets("LAVEXPORT")
    Sheets.Add(After:=Sheets("LAVEXPORT")).Name = "AM"
    Sheets.Add(After:=Sheets("AM")).Name = "PM"
    Sheets.Add(After:=Sheets("PM")).Name = "RON"

End Sub



Sub Test()
  Dim rCrit As Range
 
  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(6,30,0),EndT,TIME(15,00,0),OR(AND(F2>=StartT,G2<=EndT,F2>=StartT,F2<=EndT),AND(G2="""",F2>=StartT,F2<=EndT),AND(F2<=StartT,G2>=StartT,G2<=EndT),AND(F2>EndT,G2>=StartT,G2<=EndT)))"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("AM").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(15,01,0),EndT,TIME(23,00,0),OR(AND(G2>=StartT,G2<=EndT,F2>=StartT,F2<=EndT),AND(G2="""",F2>=StartT,F2<=EndT),AND(F2<=StartT,G2>=StartT,G2<=EndT),AND(F2>EndT,G2>=StartT,G2<=EndT)))"
    'rCrit.Cells(2).Formula = "=LET(StartT,TIME(15,01,0),EndT,TIME(23,00,0),OR(AND(G2>=StartT,G2<=EndT,F2>=StartT,F2<=EndT),AND(G2="""",F2>=StartT,F2<=EndT),AND(F2>EndT,G2>=StartT,G2<=EndT)))"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("PM").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

  With Sheets("LAVEXPORT").Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = "=LET(StartT,TIME(23,01,0),EndT,TIME(23,59,0),StartT2,TIME(00,00,0),EndT2,TIME(06,29,0),OR(AND(F2<=StartT, G2>=StartT, G2<=EndT, G2<>""""), AND(F2>=StartT, F2<=EndT, G2>=StartT, G2<=EndT, G2<>""""),AND(F2>=StartT, F2<=EndT, G2=""""),AND(F2>=StartT2, F2<=EndT2, G2=""""),AND(F2>=EndT2, G2>=StartT2, G2<=EndT2,G2<>""""), AND(F2>=StartT2, F2<=EndT2, G2>=StartT2, G2<=EndT2, G2<>""""), AND(F2>=EndT2, G2>=StartT2, G2<=EndT2, G2<>"""")))"
   .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("RON").Range("A1"), Unique:=False
    rCrit.ClearContents
  End With

End Sub