Apply Vlookup formula in all the available sheets in a workbook

[Deysam]

Hello Experts,

This is my first thread here.

I am not an expert in EXCEL/VBA but, love to gain knowledge about and by learning from people like you I have gain knowledge to the basics of VBA.

Here's my query I have two workbooks having multiple sheets. I want to apply a vlookup in Column "Q" in Book2.xlsm and the vlookup should reference data from Book1.xlsx.

I have written a code that works perfectly in sheet1 on Book2.xlsm as compared to Sheet1 in Book1.xlsx but, it doesn't works in rest of the worksheets.

Option Explicit
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim C As Integer
Dim I As Integer

C = ActiveWorkbook.Worksheets.Count

For I = 1 To C


Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")


'Determine last row of source
With sourceSheet
    SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With outputSheet
    'Determine last row in col P
  OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
    'Apply our formula
  .Range("Q2:Q" & OutputLastRow).Formula = _
        "=VLOOKUP($A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$P$" & SourceLastRow & ",3,0)"
End With
MsgBox ActiveWorkbook.Worksheets(I).Name
         
    Next I

    
'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
End Sub

The number of sheets in both the workbooks may increase or decrease. Currently there are more than 60+ worksheets in both the books. I have attached examples of how the Workbooks will look like.

Thanks
Sumit