Help with Find command - Match names on multiple tabs

[Stu96]

Hi there, new member here - self taught excel vba so still a beginner and hoping someone may be able to help point me in the right direction or be able to offer code which could work with what I need.

I am working on a spreadsheet that has to be completed quarterly so am looking to automate a lot of the process. I have a master tab - "@" and 16 Team tabs. On the @ is a table which includes all 16 team names, I would like this table to update depending on what Teams have signed off.

Quick run-through:

Spreadsheet is emailed out to multiple teams, each log in and review Products. After all products have been reviewed, they press the "Sign Off" button (there is a sign off button on each team tab however they all share the same code). This button does 3 things;

1. Inputs username and date to right of button
2. Sends email to spreadsheet owner
3. Updates table on "@" tab.

Number 3 is where I am having the issue. I have tried find, if, functions - lots of different options but just can't get it to work. Functions didn't work as the spreadsheet is reset every quarter so the cell values are cleared, so it needs to be VBA. I've also been told 'Find' is the best option but after spending days looking online still can't find anything which helps me get to what I need.

Some previous options I tried:

Sub If_Team 1()

    'Set variables
    Set sht1 = Sheets("@")
    Set sht2 = Sheets("Team 1")

    'Team1
    If sht2.Range("M2:N2") <> "" Then
        sht1.Range("C4:D4") = sht2.Range("M2:N2")
        sht1.Range("B4") = "P"
    Else
        sht1.Range("C4:D4") = ""
        sht1.Range("B4") = "O"
    End If

Unfortunately this worked until I put in more If functions, where it then pasted the data in the whole table rather than just Team 1. The below also worked, until again adding more values where it pasted the data in every field where the criteria was met (which was them all).

    If pfID = "Team 1" Then GoTo 1 Else
    If pfID = "Team 2" Then GoTo 2 Else

1   sht2.Cells(3, 2).Value = "P"
    sht2.Cells(3, 3).Value = Date
    sht2.Cells(3, 4).Value = Environ("username")

On each team tab is the team name, so lets say "Team 1". Team 1 is found in Cell "F1" on the Active Team Sheet. On the @ tab in the table, Team 1 is Cell "A3".

What I would like to happen is ActiveSheet.Range("F1") to find the same name on the @ (sht2) tab, and then do the following if the names match (so as we know Team 1 on the @ tab is "A3"):

• sht2.Range("A4").Value = "P"
• sht2.Cells("A5").Value = Date
• sht2.Cells("A6").Value = Environ("username")

This way the spreadsheet owner will only need to review the @ tab to see who has signed off, rather than go through each tab. However I don't want it to point to an individual cell like above as I would like it to find and match the names.

Here is the full code so far:

    Sub Button2_Click() 'SIGN OFF BUTTON
    Dim cellAddr As String
    Dim aCol As Long

    ' Declare variables
    Dim c As Integer            ' Column
    Dim emBody As String        ' Body text of email
    Dim emCnt As Integer        ' Count of email addressees
    Dim emTitl As String        ' Subject line of email
    Dim emTxt As String         ' List of email addressees
    Dim myOutlook As Object     ' MS Outlook application
    Dim mySendmail As Object    ' The email to be sent
    Dim pfID As String          ' Platform ID
    Dim r As Integer            ' Row

    'Set variables
    Set sht1 = ActiveSheet
    Set sht2 = Sheets("@")

    'Cell Address
    cellAddr = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
    'Column Number
    aCol = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column

    'Input Date and Username
    If aCol <> 1 Then _
    sht1.Range(cellAddr).Offset(, 2).Value = Date
    sht1.Range(cellAddr).Offset(, 1).Value = Environ("username")

    ' Obtain Platform details
    pfID = ActiveSheet.Range("F1").Value

    'Version ID
    vID = sht2.Range("D1").Value


    **'Input Sign Off on @ Tab**


    ' Email subject line
    emTitl = pfID & " - Out of Support Software Review " & vID & " Completed"
    ' Email body text
    emBody = "<BODY style=font-size:12pt;font-family:Calibri>" & "Hi," & "<br>" & "<br>" & "Out of Support Software Review " & "<b>" & vID & "</b>" & " Completed for " & "<b>" & pfID & "</b>" & "."

    Set myOutlook = CreateObject("Outlook.Application")
        Set mySendmail = myOutlook.CreateItem(olMailItem)
        With mySendmail
         .to = ""
         .Subject = emTitl
         .HTMLBody = emBody
         .Display
        End With

    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing

ErrHandler:
    '
End Sub

Any help is appreciated, any questions let me know! Sorry if this is slightly confusing.