I would like to copy data on a Master sheet to a Weekly sheet to retain the values for each week.
I have this code, but it doesn't find the date for some reason:
Dim rng As Range
Dim WEDate As String
Dim Monthws As String
Dim Weekws As String
Monthws = "Master Monthly-Weekly Totals"
Weekws = "Weekly"
WEDate = Worksheets(Monthws).Range("B4").Value 'Get Week ending Date
If WEDate = "1/8/2012" Then
MsgBox "The Dates Match"
MsgBox "The Dates Don't Match"
'WEDate = Worksheets(Monthws).Range("B4").Value 'Get Week ending Date
Set rng = Worksheets(Monthws).Range("D1:D128").Find("Weekly Running Total", LookIn:=xlValues)
rng.Offset(2, 0).Resize(rng.End(xlDown).Rows + 3).Copy
Application.CutCopyMode = False
Set rng = .Range("A1:A53").Find(What:=WEDate, LookIn:=xlValues)
If Not rng Is Nothing Then
ActiveSheet.Cells(1, 0).PasteSpecial Paste:=xlPasteValues
rng.Offset(1, 0).Resize(rng.End(xlDown).Row - 1).PasteSpecial (xlPasteAll)
1) Read Date input (End of the week date) by User in B4 on the Master Monthly-Weekly Totals sheet
2) Copy Data in Column D
3) Go to Worksheet "Weekly" and find the same date input by the user, and paste the data to that column
I think the problem here is the Find(What:=WEDate), I've tried Find(WEDate), even tried Find(CDate(WEDate)), Excel always
finds Nothing, so my paste code never executes.