ChaCha101
02-10-2023, 01:58 AM
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
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