Pulling "Find(What" from another sheet?

[jmpatrick]

Good morning!

I have a simple macro to search a named range (DateColumn) on one sheet (Calendar). As it is currently is the search is for "8/23/21" however I would like the search term to come from a specific cell (B2) on another sheet (SpacerGrid).

Here's the code I started with:

Sub SearchDate()
   Sheets("Calendar").Select
    Application.Goto Reference:="DateColumn"
    Cells.Find(What:="8/23/21", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Sheets("Calendar").Select
    Selection.FindNext(After:=ActiveCell).Activate
End Sub

...and here's my unsuccessful attempt to pull the search term from SpacerGrid!B2:

Sub SearchDate()
   Sheets("Calendar").Select
    Application.Goto Reference:="DateColumn"
    Cells.Find(What:="SpacerGrid!B2", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Sheets("Calendar").Select
    Selection.FindNext(After:=ActiveCell).Activate
End Sub

Is it possible to target a specific sheet and cell to use as the search term?

 

[sykes]

This should work (albeit untested):
NB test on a COPY, first!

Sub SearchDate()
Dim fnd As String

fnd = Sheets("SpacerGrid").Range("B2").Value

   Sheets("Calendar").Select
    Application.Goto Reference:="DateColumn"
    Cells.Find(What:=fnd, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Sheets("Calendar").Select
    Selection.FindNext(After:=ActiveCell).Activate
End Sub