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
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