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

N

navydog

New Member
Joined
Jul 22, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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!!


1690044538427.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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?
 
Upvote 0
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.

VBA Code: 
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
ABCDEFGH
1Hdr 1Arrival/DepartureRoutingScheduled TimeETHdr 6
2A2DepartureABE9:55F2
3A3DepartureABE14:18F3
4A4DepartureABE21:1221:40F4
5A5DepartureABQ8:50F5
6A6DepartureABQ20:5522:09F6
7A7DepartureABQABQ14:19F7
8A8DepartureABY12:17F8
9A9DepartureABY19:5420:24F9
10A10DepartureAEX14:50F10
11A11DepartureAEX19:5422:05F11
12A12DepartureAGS8:258:40F12
13A13DepartureAGS14:15F13
14A14DepartureAGS18:05F14
15
Sheet1


Result of the above code.

navydog.xlsm
ABCDEFG
1Hdr 1Arrival/DepartureRoutingScheduled TimeETHdr 6
2A2DepartureABE9:55F2
3A3DepartureABE14:18F3
4A5DepartureABQ8:50F5
5A7DepartureABQABQ14:19F7
6A8DepartureABY12:17F8
7A12DepartureAGS8:258:40F12
8A13DepartureAGS14:15F13
9
Sheet2
 
Upvote 0
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??

VBA Code: 
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
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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. 😊


navydog said:
I figured out my issue with the filter criteria and now it's returning the correct rows and exporting to new sheet.
Click to expand...
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.


navydog said:
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.
Click to expand...
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?
 
Upvote 0
Peter_SSs said:
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?
Click to expand...
Here is the Xl2bb code for the sample data

LAVEXPORT.xlsx
ABCDEFGHIJKLM
1Flight NumberRegstrnA/C TypeRoutingScheduled DateScheduled TimeETGateSvcSvc TimePrioLPrioService
29037/28/202321:158:10yes04:38 (07/30/23)NormalCritical
341647/28/202321:4113:35yes04:38 (07/30/23)NormalNormal
47727/28/202322:4910:00yes04:38 (07/30/23)NormalNormal
53177/29/20237:04yes04:38 (07/30/23)NormalCritical
612287/29/20237:057:10yes04:38 (07/30/23)NormalNormal
726347/29/20237:057:30yes04:38 (07/30/23)NormalNormal
820697/29/20237:05yes04:38 (07/30/23)NormalNormal
923907/29/20237:05yes04:38 (07/30/23)NormalNormal
103277/29/20237:05yes04:38 (07/30/23)NormalCritical
118397/29/20237:05yes04:38 (07/30/23)NormalCritical
1213437/29/20237:107:20yes04:38 (07/30/23)NormalCritical
1325237/29/20237:10yes04:38 (07/30/23)NormalNormal
1428967/29/20237:14yes04:38 (07/30/23)NormalNormal
1513307/29/20237:15yes04:38 (07/30/23)NormalNormal
164097/29/20237:15yes04:38 (07/30/23)NormalCritical
1725267/29/20237:16yes04:38 (07/30/23)NormalNormal
1814167/29/20237:20yes04:38 (07/30/23)NormalNormal
1916107/29/20237:20yes04:38 (07/30/23)NormalCritical
2016467/29/20237:20yes04:38 (07/30/23)NormalCritical
2120147/29/20237:28yes04:39 (07/30/23)NormalCritical
2223327/29/20237:30yes04:39 (07/30/23)NormalNormal
2323287/29/20237:40yes04:40 (07/30/23)NormalNormal
2420397/29/20237:44yes04:38 (07/30/23)NormalNormal
2513117/29/20237:458:55yes04:38 (07/30/23)NormalNormal
2626567/29/20237:56yes04:38 (07/30/23)NormalNormal
2726517/29/20237:58yes04:38 (07/30/23)NormalNormal
2816767/29/20238:00yes04:38 (07/30/23)NormalCritical
2912857/29/20238:04yes04:39 (07/30/23)NormalNormal
3015097/29/20238:108:25yes04:39 (07/30/23)NormalNormal
318987/29/20238:108:45yes04:38 (07/30/23)NormalCritical
3221077/29/20238:10yes04:38 (07/30/23)NormalNormal
3324707/29/20238:10yes04:39 (07/30/23)NormalNormal
347337/29/20238:10yes04:38 (07/30/23)NormalCritical
358497/29/20238:10yes04:39 (07/30/23)NormalCritical
369167/29/20238:10yes04:38 (07/30/23)NormalCritical
3729917/29/20238:12yes04:39 (07/30/23)NormalNormal
389657/29/20238:158:21yes04:40 (07/30/23)NormalCritical
398307/29/20238:158:35yes04:38 (07/30/23)NormalCritical
4027637/29/20238:159:55yes04:39 (07/30/23)NormalNormal
4118477/29/20238:15yes04:38 (07/30/23)NormalCritical
4221617/29/20238:15yes04:38 (07/30/23)NormalNormal
4322237/29/20238:15yes04:39 (07/30/23)NormalNormal
4423017/29/20238:15yes04:38 (07/30/23)NormalNormal
454267/29/20238:15yes04:38 (07/30/23)NormalCritical
4649857/29/20238:15yes04:39 (07/30/23)NormalNormal
477817/29/20238:15yes04:39 (07/30/23)NormalNormal
484187/29/20238:16yes04:38 (07/30/23)NormalCritical
4925197/29/20238:208:25yes04:39 (07/30/23)NormalNormal
507777/29/20238:208:40yes04:38 (07/30/23)NormalCritical
5111837/29/20238:208:50yes04:38 (07/30/23)NormalCritical
5210897/29/20238:20yes04:38 (07/30/23)NormalNormal
5312137/29/20238:20yes04:38 (07/30/23)NormalCritical
5417607/29/20238:20yes04:38 (07/30/23)NormalCritical
5523367/29/20238:20yes04:39 (07/30/23)NormalNormal
5628687/29/20238:20yes04:39 (07/30/23)NormalNormal
5754847/29/20238:20yes04:39 (07/30/23)NormalNormal
585777/29/20238:24yes04:39 (07/30/23)NormalCritical
5918927/29/20238:25yes04:38 (07/30/23)NormalNormal
6021007/29/20238:25yes04:39 (07/30/23)NormalNormal
6123447/29/20238:25yes04:39 (07/30/23)NormalNormal
6251797/29/20238:25yes04:40 (07/30/23)NormalNormal
6353427/29/20238:25yes04:40 (07/30/23)NormalNormal
6454787/29/20238:25yes04:39 (07/30/23)NormalNormal
6515467/29/20238:308:30yes04:39 (07/30/23)NormalNormal
667257/29/20238:308:50yes04:39 (07/30/23)NormalCritical
673467/29/20238:309:05yes04:39 (07/30/23)NormalCritical
6819247/29/20238:30yes04:38 (07/30/23)NormalNormal
6928177/29/20238:30yes04:39 (07/30/23)NormalNormal
705697/29/20238:30yes04:38 (07/30/23)NormalCritical
7112277/29/20238:328:44yes04:38 (07/30/23)NormalNormal
7211347/29/20238:35yes04:39 (07/30/23)NormalNormal
7317287/29/20238:35yes04:39 (07/30/23)NormalNormal
7423577/29/20238:35yes04:39 (07/30/23)NormalNormal
7526327/29/20238:35yes04:39 (07/30/23)NormalCritical
764327/29/20238:35yes04:38 (07/30/23)NormalNormal
779617/29/20238:35yes04:38 (07/30/23)NormalCritical
7820067/29/20238:369:35yes04:39 (07/30/23)NormalCritical
7924757/29/20238:388:38yes04:38 (07/30/23)NormalCritical
8019647/29/20238:409:00yes04:38 (07/30/23)NormalCritical
8112987/29/20238:40yes04:38 (07/30/23)NormalCritical
8225077/29/20238:41yes04:39 (07/30/23)NormalNormal
837867/29/20238:44yes04:38 (07/30/23)NormalCritical
8470377/29/20238:45yes04:40 (07/30/23)NormalCritical
8517097/29/20238:508:55yes04:39 (07/30/23)NormalNormal
8613547/29/20238:509:20yes04:39 (07/30/23)NormalCritical
8729527/29/20238:50yes04:39 (07/30/23)NormalNormal
8813757/29/20238:559:32yes04:39 (07/30/23)NormalCritical
8920357/29/20238:5510:55yes04:40 (07/30/23)NormalCritical
9019277/29/20238:55yes04:39 (07/30/23)NormalNormal
9123267/29/20238:55yes04:39 (07/30/23)NormalNormal
9226447/29/20238:55yes04:38 (07/30/23)NormalNormal
9399087/29/20239:009:20yes04:39 (07/30/23)NormalCritical
9424087/29/20239:009:52yes04:39 (07/30/23)NormalCritical
9521697/29/20239:00yes04:39 (07/30/23)NormalCritical
9652917/29/20239:00yes04:40 (07/30/23)NormalNormal
9799007/29/20239:00NormalCritical
9851727/29/20239:049:35yes04:40 (07/30/23)NormalNormal
9922707/29/20239:08yes04:39 (07/30/23)NormalNormal
10024677/29/20239:09yes04:39 (07/30/23)NormalNormal
10113667/29/20239:109:30yes04:39 (07/30/23)NormalNormal
10215107/29/20239:10yes04:38 (07/30/23)NormalNormal
10325177/29/20239:10yes04:39 (07/30/23)NormalNormal
10439497/29/20239:10yes04:39 (07/30/23)NormalNormal
1054397/29/20239:10yes04:39 (07/30/23)NormalNormal
10650047/29/20239:10yes04:39 (07/30/23)NormalNormal
10754577/29/20239:10yes04:40 (07/30/23)NormalNormal
1089207/29/20239:10yes04:39 (07/30/23)NormalNormal
1096487/29/20239:12yes04:38 (07/30/23)NormalNormal
1109047/29/20239:159:25yes04:39 (07/30/23)NormalCritical
11110177/29/20239:159:28yes04:38 (07/30/23)NormalNormal
11229837/29/20239:15yes04:39 (07/30/23)NormalNormal
11322077/29/20239:18yes04:39 (07/30/23)NormalNormal
11454607/29/20239:18yes04:40 (07/30/23)NormalNormal
11526827/29/20239:209:40yes04:40 (07/30/23)NormalNormal
11625437/29/20239:209:45yes04:40 (07/30/23)NormalCritical
11716217/29/20239:20yes04:39 (07/30/23)NormalNormal
11820737/29/20239:20yes04:40 (07/30/23)NormalCritical
11925477/29/20239:24yes04:39 (07/30/23)NormalNormal
12023847/29/20239:26yes04:40 (07/30/23)NormalNormal
12116587/29/20239:3010:35yes04:39 (07/30/23)NormalCritical
1228227/29/20239:409:45yes04:39 (07/30/23)NormalCritical
12313167/29/20239:409:47yes04:39 (07/30/23)NormalCritical
1245017/29/20239:409:50yes04:38 (07/30/23)NormalCritical
12517827/29/20239:4010:09yes04:39 (07/30/23)NormalCritical
12615277/29/20239:40yes04:39 (07/30/23)NormalNormal
12720777/29/20239:40yes04:39 (07/30/23)NormalCritical
1288077/29/20239:429:42yes04:39 (07/30/23)NormalCritical
12919507/29/20239:439:50yes04:39 (07/30/23)NormalCritical
13018987/29/20239:45yes04:39 (07/30/23)NormalCritical
13119377/29/20239:45yes04:38 (07/30/23)NormalCritical
13219447/29/20239:45yes04:40 (07/30/23)NormalCritical
13319707/29/20239:45yes04:40 (07/30/23)NormalCritical
13419777/29/20239:45yes04:39 (07/30/23)NormalCritical
13529497/29/20239:45yes04:39 (07/30/23)NormalNormal
1365867/29/20239:45yes04:39 (07/30/23)NormalCritical
1375887/29/20239:45yes04:39 (07/30/23)NormalCritical
1388557/29/20239:45yes04:39 (07/30/23)NormalCritical
13918157/29/20239:4610:45yes04:39 (07/30/23)NormalCritical
14027557/29/20239:509:50yes04:39 (07/30/23)NormalNormal
14116417/29/20239:5010:00yes04:39 (07/30/23)NormalNormal
14227397/29/20239:5010:05yes04:39 (07/30/23)NormalNormal
1437747/29/20239:5010:35yes04:38 (07/30/23)NormalCritical
14410617/29/20239:50yes04:39 (07/30/23)NormalNormal
14517967/29/20239:50yes04:40 (07/30/23)NormalCritical
14621827/29/20239:50yes04:40 (07/30/23)NormalNormal
14724007/29/20239:50yes04:39 (07/30/23)NormalNormal
14853417/29/20239:50yes04:40 (07/30/23)NormalNormal
14918097/29/20239:5110:11yes04:39 (07/30/23)NormalCritical
15025567/29/20239:54yes04:40 (07/30/23)NormalNormal
15110137/29/20239:5510:00yes04:40 (07/30/23)NormalNormal
15218947/29/20239:5510:15yes04:39 (07/30/23)NormalCritical
1536007/29/20239:5512:45yes04:40 (07/30/23)NormalNormal
15414327/29/20239:55yes04:39 (07/30/23)NormalCritical
15517657/29/20239:55yes04:40 (07/30/23)NormalCritical
15618827/29/20239:55yes04:39 (07/30/23)NormalCritical
15719867/29/20239:55yes04:39 (07/30/23)NormalCritical
15851937/29/20239:55yes04:40 (07/30/23)NormalNormal
15919927/29/20239:5610:45yes04:39 (07/30/23)NormalCritical
16026997/29/20239:58yes04:39 (07/30/23)NormalCritical
16119977/29/20239:59yes04:39 (07/30/23)NormalCritical
1627877/29/202310:0010:13yes04:40 (07/30/23)NormalCritical
16315637/29/202310:0010:20yes04:39 (07/30/23)NormalNormal
16417597/29/202310:0010:35yes04:40 (07/30/23)NormalCritical
16519297/29/202310:0011:01yes04:39 (07/30/23)NormalCritical
16614187/29/202310:00yes04:40 (07/30/23)NormalCritical
16749067/29/202310:00yes04:40 (07/30/23)NormalNormal
1687527/29/202310:00yes04:39 (07/30/23)NormalCritical
1698747/29/202310:00yes04:39 (07/30/23)NormalCritical
17022867/29/202310:0410:40yes04:40 (07/30/23)NormalCritical
17121477/29/202310:0510:25yes04:39 (07/30/23)NormalNormal
17223717/29/202310:0510:25yes04:39 (07/30/23)NormalCritical
17327757/29/202310:0510:30yes04:39 (07/30/23)NormalCritical
17420177/29/202310:0510:46yes04:40 (07/30/23)NormalNormal
17517717/29/202310:0511:00yes04:39 (07/30/23)NormalCritical
17624457/29/202310:05yes04:39 (07/30/23)NormalNormal
17728307/29/202310:05yes04:39 (07/30/23)NormalNormal
17852927/29/202310:05yes04:39 (07/30/23)NormalNormal
1798207/29/202310:05yes04:40 (07/30/23)NormalNormal
1805827/29/202310:0610:28yes04:40 (07/30/23)NormalCritical
18118697/29/202310:0910:34yes04:39 (07/30/23)NormalCritical
18221997/29/202310:1010:21yes04:40 (07/30/23)NormalNormal
18352627/29/202310:1010:31yes04:40 (07/30/23)NormalNormal
18424657/29/202310:1010:39yes04:39 (07/30/23)NormalCritical
18522007/29/202310:10yes04:39 (07/30/23)NormalNormal
18627007/29/202310:10noNormalNormal
18728207/29/202310:10yes04:39 (07/30/23)NormalNormal
18829087/29/202310:10yes04:39 (07/30/23)NormalNormal
1895337/29/202310:12yes04:39 (07/30/23)NormalNormal
1903237/29/202310:1510:25yes04:39 (07/30/23)NormalCritical
19116607/29/202310:1510:40noNormalNormal
19254387/29/202310:1512:00yes04:40 (07/30/23)NormalNormal
19323987/29/202310:15yes04:40 (07/30/23)NormalNormal
1943247/29/202310:15yes04:39 (07/30/23)NormalCritical
1959457/29/202310:15yes04:38 (07/30/23)NormalCritical
1969957/29/202310:15yes04:38 (07/30/23)NormalCritical
19719587/29/202310:1611:45yes04:40 (07/30/23)NormalCritical
19819837/29/202310:18yes04:39 (07/30/23)NormalCritical
19921777/29/202310:2010:50yes04:39 (07/30/23)NormalNormal
20011537/29/202310:20yes04:39 (07/30/23)NormalNormal
Sheet4
 
Upvote 0
Peter_SSs said:
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?
Click to expand...
Here is the current code that I have split into 3 different modules...

VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
406
Peter_SSs
Peter_SSs
U
  • Question Question
Excel VBA, Loop, Copy, Loop, Paste, Autofilter, return value, Loop.
Replies
0
Views
99
user29383902
U
U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
49
user29383902
U
K
  • Question Question
[VBA] Applying filter to multiple tables on same sheet
Replies
2
Views
307
kleinmat
K
M
  • Question Question
VBA copy cell ranges and paste each cell n times into a different sheet
Replies
4
Views
40
Joe4
Joe4

Forum statistics

Threads
1,224,740
Messages
6,180,678
Members
452,993
Latest member
FDARYABEE

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top