VBA-Successive vLookup with multiple sheets for data source

[winrow]

Hello everyone,

I would need your help in vba with vLookup. I have one workbook with
- One worksheet datasource to not modify
- 11 worksheets (number 2 to 12) named from "01" till "11" i.e. outcomes of the questions with relevant range B2:C
- a last worksheet named "compilation"

The worksheet compilation is structured as:
First row are the respective headers
Column A: List of unique participants
Column B: vlookup of participants to question 01
Column C: vlookup of participants to question 02
.
.
.
Column L: vLookup of participants to question 11

I wish to make successive vlookup to populate the range in worksheet compilation. so far I have a code that will fill the column B i.e. question 01. I am grateful for any suggestions you could have

Thanks!

[COLOR=#000000][FONT=Calibri]Sub vlookup_sourcedata_in_<wbr>differentsheets()[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim SLR As Variant[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim OLR As Variant[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim sourceSheet As Worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim outputSheet As Worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Dim i As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]'What are the names of our worksheets?
Set sourceSheet = Worksheets("01") ' this should Loop from worksheet number 2 to 11[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Set outputSheet = Worksheets("Compilation")[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]OLR = outputSheet.Cells(Rows.Count, 1).End(xlUp).row[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Debug.Print OLR[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]For i = 2 To 12[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]        Set sourceSheet = ThisWorkbook.Worksheets(i)[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    'Determine last row of source[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    SLR = sourceSheet.Range("A1").Cells(<wbr>Rows.Count, "A").End(xlUp).row[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]   [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    Debug.Print SLR[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    Debug.Print sourceSheet.Name[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    With outputSheet
' Range should then move to next column[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]        .Range("B3:B" & OLR).Formula = _[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]        "=VLOOKUP(A3,'" & sourceSheet.Name & "'!$B2:$C" & SLR & ",2,0)"[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    End With[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]    [/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]Next i[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri]End Sub[/FONT][/COLOR]

 

[Fluff]

Not sure if I've understood you correctly, but try this

    With outputSheet
' Range should then move to next column
        .Range(.Cells(3, i), .Cells(OLR, i)).Formula = _
        "=VLOOKUP(A3,'" & sourceSheet.Name & "'!$B2:$C" & SLR & ",2,0)"
    End With

 

[winrow]

Hi Fluff,

You did. It is working perfectly! Thank you for your prompt answer!

 

[Fluff]

Glad to help & thanks for the feedback