CODEWITHSUNDEEP
excelvbavariablesvlookup
Sherbetdab
Sherbetdab

Reputation: 127

Vba Vlookup with variable sheetname

I have a workbook where i have the following formula in cell D3.

"=VLOOKUP($C3,'[NIGHT ROTA.xlsx]15'!$A$5:$I$32,D$1,0)"

The source workbook has 52 sheets and when i want to search a certain sheet i have to change the sheetname manually and then run the code below to copy it across the cells in my workbook using worksheet change.

Is there a way to have the sheetname as a variable that i would have in a cell in my workbook or that would be chosen from a drop down? I know i could use INDIRECT but don't want to have to open the source workbook.

Sub WeekChange()
Range("D3").Select
Selection.Copy
Range("D3:I26").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

Upvotes: 0

Views: 1849

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You can put the sheet names in a drop-down cell, say E1 (or any of your choice) and capture the change in this cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("E1").Address Then
        Range("D3:I26").Formula = "=VLOOKUP($C3,'[NIGHT ROTA.xlsx]" & Target.Value & "'!$A$5:$I$32,D$1,0)"
    End If
End Sub

To populate the drop-down box in E1, you can eventually set it upon the opening of the workbook, but since you dont want to open it (I guess you have some reasons), you might have to populate the validation of E1 manually.

Upvotes: 1

Related Questions