Copy cells from one workbook to another with matching date

I am looking for help on this one.

I am trying to Copy several cells from one workbook to another workbook by matching date values.

Both workbooks already have the date in column B.

I have a total of two folders, C; Timesheets\JAsher Timesheet.xls in one folder and

C: Time Master\ Dev Time Master.xls in the other.

JAsher Timesheet.xls has a worksheet named "Timesheet" and data begins on row 9.

Column "A" is the weekday, Column "B" is the date, Column "C":"N" is data such as regular hours, sick hours, vacation hours and so on.

Dev Time Master.xls has a worksheet for each employee that has 42 rows for each month and skips a row before starting another month.

I will have 15 additional worksheets named after all employees.

Dev Time Master.xls has a worksheet named "JAsher"and data begins on row 5 in this worksheet.

Column "A" is weekday, Column "B" is Date, Column "C":"N" are cells that I want to contain the copied value from the workbook "JAsher Timesheet.xls".

I am looking for the process to have the macro compare dates in column "B" for both workbooks and then copy the cells in the row that matches the date between "Timesheet" & "JAsher".

I would like the macro to copy and paste or replace data in the rows that match the date.

This macro would have to open Dev Time Master.xls, copy the data selected from JAsher Timesheet.xls and then close the Dev Time Master.xls workbook, then close the JAsher Timesheet.xls workbook.

If an employee runs this macro with the same date as before, it would simply overwrite the data in the row.

After every 42 rows there is a blank row with a month name in it.

I am wanting to have this data grow in the Dev Time Master.xls for the entire calendar year.

Here is an example of my attempt. I suspect that I am nowhere near being in the right place.

Thanks for any help you can share.

Sub CopytoMaster()
'
' CopytoMaster Macro
' Copy to Master
'
Dim LDate As String
Dim LColumn As Integer
Dim LFound As Boolean
 
' Retrieve date value to search for
LDate = Sheets("JAsher").Range("B5").Value
 
Sheets("JAsher").Select
 
'Start at column B
LColumn = 2
LFound = False
 
While LFound = False
 
'Encountered blank cell in row 2, terminate search 
If Len(Cells(2, LColumn)) = 0 Then MsgBox "No matching date was found."
Exit Sub
'Found match in row 2
ElseIf Cells(2, LColumn) = LDate Then
 
'Select values to copy from "Timesheet" sheet 
Sheets("Timesheet").Select 
Range("B9:N9").Select Selection.Copy
 
'Paste onto "Plan" sheet
Sheets("JAsher").Select
Cells(3, LColumn).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
 
LFound = True
MsgBox "The data has been successfully copied."
 
'Continue searching
Else
LColumn = LColumn + 1
End If
 
Wend
 
End Sub
"
 
'Continue searching
Else
LColumn = LColumn + 1
End If
 
Wend
 
End Sub
Almir's picture

Copy cells from one workbook to another with matching date

Very detailed explanation, I must confess. However, I think it would be much easier to move from the point blank if you send an example file. On the first sight, I would try with Advanced Filter within macro, but send an example for the beginning.