June 4, 2015

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


One thought on “Copy Data from One Excel Sheet to Another using Time Criteria

  1. Dear Sir,
    i have a data base of share prices in one sheet where in the first column carries the name of the 500 companies and in next columns monthly prices from january 1993 to dec. 2013. Now in the other sheets i have a list of about 100 companies sorted from first sheet but not in the same order. Now i need to import share prices for these 100 companies from the first sheet for any two months e.g. april 2000 in one column and dec 2000 in the next column. please help because i have more than 100 different such lists asking prices for different months.

Comments are closed.