VBA: Open another workbook and find date in column of dates

[TaskMaster]

Good afternoon,

I have a spreadsheet that holds data sorted by dates. I am wanting to open another workbook and search for the date in that column to use as a reference to pull data into my current workbook.

Dim FindRow As Range
Dim FindRow1 As Range
Dim ColRef As String
Dim ColRef1 As String

Set FindRow = ThisWorkbook.Worksheets("Summary").Rows(2).Find(What:="01/11/2022", LookIn:=xlValues, LookAt:=xlWhole)
Set FinRow1 = ThisWorkbook.Worksheets("Backing Data").Columns("A").Find(What:="01/11/2022", LookIn:=xlValues)

ColRef = Split(Cells(, FindRow.Column).Address, "$")(1)
ColRef1 = FindRow1.Row

ThisWorkbook.Worksheets("Summary").Range(ColRef & "5").Value = ThisWorkbook.Worksheets("Backing Data").Range("G" & ColRef1).Value

The above works perfectly in the current spreadsheet however when trying to do this referencing another workbook using the below im getting a run time error.

  With Workbooks(Worksheets("Summary").Range("AC1").Value).ActiveSheet 'Holds current worksheet name
      Set Wbk = Workbooks.Open("C:\Users\Flow\Desktop\Test\Daily Stats.xls", ReadOnly:=True) ' opens workbook to copy data from
     Set DStats = Workbooks("Daily Stats.xls").Worksheet(TName).Columns("A").Find(What:="01/11/2022", LookIn:=xlValues, LookAt:=xlWhole) ' Error here Run-time error '438': Object doesm't support this property or method
      DS = DStats.Row
      .Range(ColRef & "10").Value = Workbooks("Daily Stats.xls").Worksheet(TName).Range("G" & DS).Value
      Wbk.Close False
   End With