Copy Data from One Excel Sheet to Another using Time Criteria

How to copy data from one Excel Sheet to Another Automatically using Time Criteria with VBA: Great tutorial.  I have an Excel file with 88,000 rows of data collected on an hourly basis. I need only data collected at 12:00:00 PM each day. So, I need to extract every 24th row into another work sheet.  Can this be done easily? I’ve already tried Advanced Filter using “12:00:00 PM” as the filter criteria but, unfortunately, the column containing the time also has the date (ex. January 1, 2004 12:00:00 PM) so “12:00:00” isn’t recognized by the filter.

Extracting the time portion from a date and time entry requires the declaration of two variables. One is a string variable into which we get the time part like this:

Dim strTime As String

strTime = Format(Cells(i, 2), “hh:mm:ss AMPM”)

Next using a date variable we get the TimeValue using the string extracted via the string variable.

Dim myTime As Date

myTime = TimeValue(strTime)

Of course, we need to loop through all the data in the worksheet and here we use the ‘for next’ looping for total automation.

Watch the Excel training video before you study the VBA or macro code step by step:

Watch the video on YouTube.

Here’s the complete VBA code to transfer data from one worksheet to another automatically using Time criteria:

Sub copyTime()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Dim erow As Long
Dim myTime As Date
Dim strTime As String
For i = 2 To lastrow
strTime = Format(Cells(i, 2), “hh:mm:ss AMPM”)
myTime = TimeValue(strTime)
If myTime = “12:00:00 PM” Then
Range(Cells(i, 1), Cells(i, 2)).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
End If
Next i
End Sub

Further reading:

Excel VBA Date & Time Functions; Year, Month, Week & Day Functions