Identify and call on excel sheets dynamically

I'm trying to build some code that will do some Vlookups between a variable number of sheets. I'd like to do this dynamically since the number of sheets in the workbook can and will be different.

Example: I have a workbook that has 3 sheets named "FOB 1", "FOB 2", "FOB 3", "PPD 1" and "PPD 2" I would then like to Vlookup column C from FOB 2 to FOB 1 and since it also exists I'd like to Vlookup FOB 3 to FOB 2 and FOB 1. And the same for the PPD sheets. (So PPD 2 vlookup into PPD 1). Sheet names will always start with FOB or PPD. Each FOB or PPD could have any number of sheets but usually no more than 4 total sheets per type.

I am currently using a count that counts the number of sheets and I'm using a loop cycle through the sheets but I think what I need is a way to identify each sheet name for the vlookup? Is this possible, this isn't something I've done before. Any help or shove in the right direction would be greatly appreciated.

Current Code:

Sub TEST()
Dim wb As Excel.Workbook
Dim SheetCount As Integer
Dim I As Integer
Dim lrow As Long

SheetCount = ActiveWorkbook.Sheets.Count
Set wb = ActiveWorkbook

For I = 1 To SheetCount
With wb
.Worksheets(I).Activate
lrow = Cells(Rows.Count, 2).End(xlUp).Row

    If .Worksheets(I).Name Like "FOB *" And .Worksheets(I + 1).Name Like "FOB *" Then
       .Worksheets(I + 1).Range("C:C").Insert Shift:=xlToRight
       .Cells(2, 3).Resize(lrow - 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'FOB 1'!C:C,1,0)"
    Else
    'do same thing for PPD sheets
    End If
 End With
Next I
ActiveWorkbook.Worksheets(1).Activate
END SUB

Solution

Here's a basic framework for how you might compare pairs of sheets in turn:

Sub Tester()
    Dim wb As Workbook, ws As Worksheet, wsPrev As Worksheet, col As Collection
    Dim itm, i As Long, p As Long
    
    Set wb = ActiveWorkbook
    
    For Each itm In Array("FOB", "PPD")
        Set col = MatchedSheets(wb, CStr(itm)) 'get matched worksheets from `wb`
        If col.Count > 1 Then            'any sheets to compare?
            For i = 2 To col.Count       'start with #2
                Set ws = col(i)          'the worksheet to operate on
                For p = 1 To i - 1       'loop all previous sheets
                    Set wsPrev = col(p)
                    Debug.Print "Comparing " & ws.Name & " with " & wsPrev.Name
                    'compare ws with wsPrev and delete matches from ws
                Next p
            Next i
        End If
    Next itm
End Sub

'Return a collection of all worksheets named like "txt*" from `wb`
Function MatchedSheets(wb As Workbook, txt As String) As Collection
    Dim ws As Worksheet
    Set MatchedSheets = New Collection
    For Each ws In wb.Worksheets
        If InStr(1, ws.Name, txt, vbTextCompare) = 1 Then
            MatchedSheets.Add ws
        End If
    Next ws
End Function