VBA code to paste based on date column

[RW11700]

I have a nightly report that is generated in excel and I am hoping to copy a cell range from sheet James and paste it to another sheet James2 based off the current date. In column A of James2 I have dates in mm/dd/yyyy format, I am hoping to take my copied cell range from James and paste them to James2 starting in column B based on TODAYS date in column A. I am able to copy the cells based on the below code but I am unable to figure out how to paste the data in sheet James2

Workbooks("Daily Availability TEST FILE.xlsm").Worksheets("James").Range("D8:X8").Copy

 

[RW11700]

OK, your original question and your screen images don't quite match up (regarding which columns you are copying), so this code may need to be altered slightly.
But this should do what you need:

Sub MyCopyMacro()

    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim curDate As Date
    Dim rng As Range
    Dim r As Long
  
'   Set worksheet variables
    Set sh1 = Sheets("James")
    Set sh2 = Sheets("James2")
  
'   Get date out of Range A3 on first sheet
    curDate = Right(Trim(sh1.Range("A3")), 10)
  
'   Find date in column A on second sheet
    Set rng = sh2.Columns("A:A").Find(What:=curDate, After:=sh2.Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
  
'   Check to see if match found
    If rng Is Nothing Then
        MsgBox "Could not find date " & Format(curDate, "m/d/yyyy") & " on Sheet " & sh2.Name, vbOKOnly, "ERROR!"
        Exit Sub
    End If
  
'   Copy data over
    sh1.Range("C8:W8").Copy sh2.Cells(rng.Row, "B")

End Sub

Unfortunately I received an object defined error.

 

[RW11700]

Joe thank you for your help! I got it to work with the following code.

Sub MyCopyMacro()

    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim curDate As Date
    Dim rng As Range
    Dim r As Long
 
'   Set worksheet variables
    Set sh1 = Sheets("James")
    Set sh2 = Sheets("James2")
 
'   Get date out of Range A3 on first sheet
    curDate = Right(Trim(sh1.Range("A3")), 10)
 
'   Find date in column A on second sheet
    Set rng = sh2.Columns("A:A").Find(curDate, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
 
'   Check to see if match found
    If rng Is Nothing Then
        MsgBox "Could not find date " & Format(curDate, "m/d/yyyy") & " on Sheet " & sh2.Name, vbOKOnly, "ERROR!"
        Exit Sub
    End If
 
'   Copy data over
    sh1.Range("C8:W8").Copy sh2.Cells(rng.Row, "B")

End Sub

 

[Joe4]

You are welcome!
Glad I was able to help.