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! :bow: