Using sheet codename in vlookup funtion

[zohaib]

Hello,

I have modified the code below to say the sheet code name instead of using the actual sheetname bc actual sheetname is variable. When I run this code it gives me a pop up window? Can anyone tell me how to fix this issue? Thanks

Sub example()


    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'Sheet3'!C[-6],1,FALSE)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-7],'Sheet4'!C[-7],1,FALSE)"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-8],'Sheet5'!C[-8],1,FALSE)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-9],'Sheet6'!C[-9],1,FALSE)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-10],'Sheet2'!C[-9],1,FALSE)"
End Sub

 

[Chihiro]

What sort of popup comes up? Is it Update Values window?

If so, likely you don't have exact sheet name in the workbook and thus Excel gives popup to have you select a file.

Make sure you have sheets name referenced in the code. "Sheet2" to "Sheet6".
Or adjust sheet name as needed.

 

[zohaib]

Hello Chihiro,

Yes it is the update values window. Sheet names are different and variable so I want to use sheet codename. For ex. sheet3 name is "edit statistics 02.09.16" but I want to use sheet code name.

Thanks,
Zohaib

 

[Chihiro]

Change your code to something like....

Sub example()

With ActiveSheet
    .Range("G2").FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'" & Sheets(3).Name & "'!C[-6],1,FALSE)"
    .Range("H2").FormulaR1C1 = _
        "=VLOOKUP(RC[-7],'" & Sheets(4).Name & "'!C[-7],1,FALSE)"
    .Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-8],'" & Sheets(5).Name & "'!C[-8],1,FALSE)"
    .Range("J2").FormulaR1C1 = _
        "=VLOOKUP(RC[-9],'" & Sheets(6).Name & "'!C[-9],1,FALSE)"
    .Range("K2").FormulaR1C1 = _
        "=VLOOKUP(RC[-10],'" & Sheets(2).Name & "'!C[-9],1,FALSE)"
End With
End Sub

 

[Marc L]

Hi !

As VLOOKUP worksheet function needs worksheet name,
better is to use it from its CodeName like​

    Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-6],'" & Sheet3.Name & "'!C[-6],1,FALSE)"

Do you like it ? So thanks to click on bottom right Like !
​

 

[zohaib]

Hello,

When I ran the macro it works fine till it gets to this line and gives me the following Run-time error 1004: method 'select' object'_worksheet' failed. fyi, i am using sheet code name instead of actual name.

here is the code it stops working at:

    Sheet1.Select
    Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-6],'" & Sheet3.Name & "'!C[-6],1,FALSE)"
    Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-7],'" & Sheet4.Name & "'!C[-7],1,FALSE)"
    Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-8],'" & Sheet5.Name & "'!C[-8],1,FALSE)"
    Range("J2").FormulaR1C1 = "=VLOOKUP(RC[-9],'" & Sheet6.Name & "'!C[-9],1,FALSE)"
    Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-10],'" & Sheet2.Name & "'!C[-9],1,FALSE)"

 

[Chihiro]

Don't use select where possible.
If it's going to be run on active sheet only, then just use Range... portion.

If you want to run it on Sheet1 always without it being active....

Sub Test()
With Sheet1
    .Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-6],'" & Sheet3.Name & "'!C[-6],1,FALSE)"
    .Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-7],'" & Sheet4.Name & "'!C[-7],1,FALSE)"
    .Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-8],'" & Sheet5.Name & "'!C[-8],1,FALSE)"
    .Range("J2").FormulaR1C1 = "=VLOOKUP(RC[-9],'" & Sheet6.Name & "'!C[-9],1,FALSE)"
    .Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-10],'" & Sheet2.Name & "'!C[-9],1,FALSE)"
End With
End Sub

 

[zohaib]

Chihiro,

If I ran this code alone it works fine but when I add it to the end on my full code it doesnt work. I cant tell why it is doing this.

 

[Chihiro]

Without looking at your full code. It's hard for me to tell what's wrong with it. You can either post your full code here, or if you prefer, you can start a private conversation.

 

[zohaib]

Chihiro,

I sent you p.m.
Thanks,
Zohaib