reference multiple tables within search range

[Icesurf3r]

I'm using the below code to check for existing records (in case the user of my UserForm clicks "add record" instead of "Update"

'Checks for existing record in case user clicks Add record instead of update.
Dim reference As String
reference = UserForm1.Ref_Num.Value
Dim aCell As Range




'Set aCell = Sheets("Tracker").Range("Table_Main[Reference Number]") &
Set aCell = Sheets("Go").Range("Table_Go[Reference Number]").Find(What:=reference, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)


    If Not aCell Is Nothing Then
        MsgBox "This record already exists" & vbNewLine & "Please select Update instead", vbCritical = vbOKOnly, "Warning"
        Exit Sub
    End If

The code works as it should, however I can't figure out how to reference the other 5 tables (which are on separate sheets) in the same sub routine as I need to search the reference number column of each table to see if there's a match.

If anyone could help I would appreciate it.

 

[Fluff]

How about

    Set aCell = Sheets("Go").Range("Table_Go[Reference Number]").Find(What:=reference, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "This record already exists" & vbNewLine & "Please select Update instead", vbCritical = vbOKOnly, "Warning"
        Exit Sub
    End If
    
    Set aCell = Sheets("Tracker").Range("Table_Main[Reference Number]").Find(What:=reference, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not aCell Is Nothing Then
        MsgBox "This record already exists" & vbNewLine & "Please select Update instead", vbCritical = vbOKOnly, "Warning"
        Exit Sub
    End If

 

[Icesurf3r]

Thanks Fluff,

Works really well.

 

[Fluff]

You're welcome & thanks for the feedback.