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
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.