Vlookup table to copy sheet to other workbook vba

Question

I am working with two workbooks. In one workbook I have a template and a lookup table. In the other workbook I have many sheets with only a few named alpha.

The lookup table has a column for the alpha sheet names, and two other columns with values to rename the sheet.

I essentially need to copy a template and paste it in the second workbook for each sheet that has alpha in the name and rename the template to the values in the lookup table. I'm currently having a problem getting the vlookup to work. Though ws.name is returning the right values, my "a" value is not matching up even to the first column which should be a 1 to 1 match.

Below is my code so far.

For Each ws In ActiveWorkbook.Sheets

    If LCase(ws.Name) Like "*alpha*" Then

                a = Application.WorksheetFunction.VLookup(ws.Name, Sheet1.Range("A2:D24"), 1)

                Debug.Print ws.Name
                Debug.Print a 
     End If
Next ws

Could it be that you're attempting your vlookup() in the same ActiveWorkbook's Sheet1? I.e., you're working with 2 different workbooks here, right? Do you need to distinguish them in this code? Or am I misunderstanding what you're trying to accomplish? — Marc

Daniel Souza Daniel Souza · Accepted Answer · 2018-05-02T15:30:25

Probably because you're lacking the last parameter of vlookup, which causes the function to return aproximate values when it doesn't match to your range, so you need to add ", FALSE" before close your parenthesis

Vlookup table to copy sheet to other workbook vba

1 Answers