Vlookup in Different Workbook - Sheet Looping

[ChaCha101]

Hi,

I have two workbooks with 60+ Tabs with the same names. I am trying to get data from the one Workbook to the other Workbook using vlookup. It needs to loop through the sheets of both workbooks to know which data needs to be added to which sheet.

Currently, my code is this:

Sub Vlookup_Jan23()
' Vlookup_Jan23 Macro
Dim i As Long
Dim shtCount As Long
shtCount = Sheets.Count
For i = 1 To shtCount
   If Sheets(i).Name <> "Workbook Contents" Then
      If Sheets(i).Name <> "Portfolio" Then
         Application.DisplayAlerts = False
         Sheets(i).Select
         Range("CS12").Select
         ActiveCell.FormulaR1C1 = "'Jan 23"
         Range("CS15").Select
         Range("CS15").Select
         ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP(""Monthly Offtaker Revenue"",'[Report - 31 Jan 2023.xlsx]170'!R11C2:R49C17,14,FALSE),0)"
         Range("CS19").Select
         ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP(""Cell Owner Distributions"",'[Report - 31 Jan 2023.xlsx]170'!R11C2:R49C17,14,FALSE),0)"
         Range("CR17").Select
      End If
   End If
  Next i
End Sub

It works fine, but the Green highlighted Sheet name needs to preferably also be sheet(i)

How can this be corrected.

Thanks

Chacha

[arnelgp]

try:

...
...
         ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP(""Monthly Offtaker Revenue"",'[Report - 31 Jan 2023.xlsx]" & Sheets(i).Name & "'!R11C2:R49C17,14,FALSE),0)"
         Range("CS19").Select
         ActiveCell.FormulaR1C1 = "=IFNA(VLOOKUP(""Cell Owner Distributions"",'[Report - 31 Jan 2023.xlsx]" & Sheets(i).Name & "'!R11C2:R49C17,14,FALSE),0)"
...
...

[Aussiebear]

One assumes here that you only have three sheets per workbook, if so then why not name the actual sheet?

[ChaCha101]

Amazing! Thanks so much!