Copy And Paste Row Into New Worksheet Based On User Listbox Selection

Hi All,

Very new to VBA here and I am having a bit of trouble with this.

I have created a UserForm which has a list box that contains dates of various "jobs" to be completed. The details of each job are contained within a row (i.e. row 1 contains all information for job 112; row 2 contains all information for job 113 etc.) Column "C" of each row contains the dates that will be selected in the list box.

I want to arrange it so that when the User selects a specific date, all the "jobs" correlating with that date (i.e. can be more than one job) are selected, copied, and pasted into a new worksheet.

This is my code at the moment:

Private Sub cmdViewJob_Click()

Const DATE_COLUMN As String = "C"
Dim LastRow As Long
Dim cell As Range
Dim strSelectedDate As String

strSelectedDate = lstJobDate.Value
JobInformationSheet = ActiveSheet.Name

With Worksheets(JobInformationSheet)
LastRow = .Cells(.Rows.Count, DATE_COLUMN).End(xlUp).Row

For Each cell In Range("C2:C" & LastRow)
If cell.Value = strSelectedDate Then
cell.EntireRow.Copy PrintJobs.Cells(LCopyToRow, 1)

End If
Next
End With
End Sub


I should add that, as it stands, this code does not give me any errors. It runs fine. It just doesn't select, copy, or paste, anything to the new sheet.

If you need any more information, just let me know!
Any help would be greatly appreciated!
Thanks.

Sorry to be a pain, but does anybody have any ideas on this one?

Originally Posted by TMShucks

I suspect the clue is in the variable types. You have a string variable to accept the date from the form. Fair enough. But a "real" date is a number. So you are comparing a string to a number and they won't match. Try converting the date to a text string using Application.WorksheetFunction.Text(...). You'll need to ensure the format used matches the format of the input.

Regards, TMS

You were right on!
Changed the string variable to a date variable (Just changed strSelectedDate As String to dtmSelectedDate As Date, and replaced it in the code).
Thanks a million!