VBA pulling data from a dataset to generate many templates pre-filled

[Fiddling4033]

Hey all, it's my first time properly using VBA and at a bit of a loss on how to conceptualize this task. Basically I have an excel workbook with a large dataset on one sheet, containing unique names by Row and 10 columns with 'responses' provided by the names.
On the same workbook I have a template file that I want to generate a unique excel file for each name in the dataset, prefilling the 10 columns into the template. (Though it will be transposing the data from one row and 10 columns into one column and 10 rows).

Sub generateSMEResponses()
Dim wb As Workbook, sh1 As Worksheet, lr As Long, rng As Range

'Variables
Set sh1 = Sheets("data") 'dataset
Set sh2 = Sheets("Instructions") 'Copy over
Set sh3 = Sheets("2022 Questions")'Copy over
Set Sh4 = Sheets("Additional Info") 'Copy over

lr = sh1.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("data").Range("A2:A" & lr)
    For Each c In rng
        sh2.Copy 
        sh3.Copy After:=Sheets("Instructions")
        Sh4.Copy After:=Sheets("2022 Questions")
        sh1.Copy After:=Sheets("Additional Info")
        Set wb = ActiveWorkbook
        wb.Sheets(1).Range("B9") = c.Value
        

        Table1 = wb.Sheets(2).Range("C11:C23") ' Range of column headers for code to lookup and identify in dataset
        Table2 = wb.Sheets(4).Range("A2:E") ' Range of names and 10 response columns
        TempRow = wb.Sheets(2).Range("F11").Row
        TempClm = wb.Sheets(2).Range("F11").Column 'Column to begin inputting identified data from above
        For Each cl In Table1
            Set tempValue = Application.WorksheetFunction.VLookup(c.Value, Table2, 2, False)
            'TempRow = TempRow + 1
        Next cl
        

        wb.SaveAs c.Value & ".xlsx"
        wb.Close False
        
    Next
End Sub

So far, the code works in copying my needed sheets, and placing the c.Value in B9. Without the loop, it also saves and generates the excel files as needed. My only struggle is working out how to actually lookup the data I need relative to each name, and input it back onto the correct sheet. Is a VLookup the correct method here?
For reference I receive errors at the VLookup line stating "Run time error 1004: Unable to get the VLookup property".

Many thanks in advance

 

[Fiddling4033]

I also couldn't work out how to lookup the data from the workbook running the macro - So my workaround was to copy the dataset onto each new workbook, have the lookup take the necessary info, then delete the dataset sheet before saving and closing.