CDate and Named Range

[Eskypades]

I have a spreadsheet with data tabulated into weekly summaries. Each week, a user will need to have the values from the weekly summary pasted into another worksheet. I am trying to build a macro that will copy the values (in this case, the values are in the named range "WkTotal") and paste them into the second worksheet. However, it first needs to find the respective row for a given week, then paste the values in that row.

So for example, Column A contains the week ending dates from1/2/11 to 4/3/11 (also called "RunSummWE"). Columns B through M are where the week's values will be pasted from WkTotal. The respective date is found in a cell named WESelect. The macro should look at the date in WESelect, find the corresponding date in RunSummWE, and paste the values into that row.

I'm having trouble using the MATCH function in VBA. Here is what I have so far in the code (obtained from searching on this board). Keep in mind that this code isn't performing the entire task; it's only trying to find the correct row.

Sub testmyvalue()

Dim myvalue As Long
Dim mydate As Date
mydate = CDate(WESelect)
Sheets("Running Summary").Activate
myvalue = Application.Match(CLng(mydate), Range("RunSummWE"), 0)

End Sub

I haven't used the CDATE function before so I'm not sure if I'm misusing it. It doesn't seem to like my using a named range instead of a specific date. If I entered a date such as "1/16/11" instead of WESelect, it works fine. Is there another way to go about accomplishing this?

Thanks in advance,
Stephen

 

[Eskypades]

After some more searching through the forum, I was able to piece together some code that does the trick. Here it is for future reference including the copy and paste part that I mentioned earlier (names have been changed to protect the innocent):

Sub findrow()

Dim mydate As Long
mydate = Range("SelectDate").Value
Dim Find_Row As String
    Sheets("Sheet2").Activate
    Find_Row = Application.Match(CLng(mydate), Range("RangeofDates"), 0)
    Application.Goto Reference:="CopyRange"
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B" & Find_Row).Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Application.CutCopyMode = False

End Sub