VBA VLOOKUP for Changing File Name

[ExelTime]

A while ago, I created a master file that does a VLOOKUP and copies and does a bunch of other stuff. Things have changed and now I am updating the VBA code to accomodate a changing file name (every year). How would I update the attached code?

I will be going from "Master_Report.xlsm" and will be changing the file name to reference the year on a specific cell. I updated it to the following in most of the code "Master_Report_" & Range("T6").Value & ".XLSM"

Is there a way to adapt the new workbook name into the VLOOKUP line that is attached? I have tried a few things, but none them worked.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[Master_Report.xlsm]Home Page'!$BC$50:$BD$343,2,FALSE)"

 

[Joe4]

Welcome to the Board!

If I understand your correctly, try this:

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[Master_Report_" & Range("T6").Value & ".xlsm]Home Page'!$BC$50:$BD$343,2,FALSE)"

The key is all literal text goes between the double-quotes, and all references/variables do not, and you concatenate them together with ampersands (&).

 

[ExelTime]

Thank you. I think this would have solved my problem, but I have to go a different route due to some other issues.
I am not sure if I need to start a new thread, but I was wondering if there was a way to use the "ThisWorkbook" command within that line (versus calling out the workbook title).
The issue is I can not always reference that cell during my code, so it errors out sometimes. I attempted to just use ThisWorkbook, but it opens a window and doesn't work properly.

Any insight would be much appriciated.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[ThisWorkbook]Home Page'!$BC$50:$BD$343,2,FALSE)"

 

[Joe4]

I am not sure if I need to start a new thread, but I was wondering if there was a way to use the "ThisWorkbook" command within that line (versus calling out the workbook title).

No. You are creating an Excel formula with this code. Can you use "ThisWorkbook" in an Excel formula that you type into Excel directly? No.
"ThisWorkbook" is strictly used in VBA, and cannot be used/embedded within Excel formulas that are put on the worksheet.

However, you should be able to use it to return the Workbook name in VBA, and return that value and insert the actual value in your formula, i.e.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[" & ThisWorkbook.Name & "]Home Page'!$BC$50:$BD$343,2,FALSE)"

The issue is I can not always reference that cell during my code, so it errors out sometimes. I attempted to just use ThisWorkbook, but it opens a window and doesn't work properly.

I do not understand this. Why would you not be able to reference the cell?
Is the cell on a different sheet or in a different file from where you are placing this VLOOKUP formula?

 

[ExelTime]

No. You are creating an Excel formula with this code. Can you use "ThisWorkbook" in an Excel formula that you type into Excel directly? No.
"ThisWorkbook" is strictly used in VBA, and cannot be used/embedded within Excel formulas that are put on the worksheet.

However, you should be able to use it to return the Workbook name in VBA, and return that value and insert the actual value in your formula, i.e.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[" & ThisWorkbook.Name & "]Home Page'!$BC$50:$BD$343,2,FALSE)"

I do not understand this. Why would you not be able to reference the cell?
Is the cell on a different sheet or in a different file from where you are placing this VLOOKUP formula?

The issue I run into when running the code as you recommended, is that at this step I get a pop up window (file explorer) to "Update Values: Master_Report_.xlsm"

It does not pull in the value in the cell when running and appears to break (ie. there is no year) It should read Master_Report_2021.xlsm

 

[Joe4]

Please answer this question I asked in my previous reply:

Is the cell on a different sheet or in a different file from where you are placing this VLOOKUP formula?

It is absolutely critical for us to know the following, if you want us to help you:
1. Exactly where this VBA code resides (in what file)?
2. Exactly where this VLOOKUP formula is being placed (in what file and on what sheet)?
3. Exactly where this VLOOKUP file should be looking for the value (in what file and on what sheet)?

Based on those answers, we may also need to know how we can determine what logic to use for the VLOOKUP formula so it can indicate which file it should be looking at.

 

[ExelTime]

Good Day Joe4,
Code background: The code starts from file "Master_Report_2021" by using a button on the "Home Page" tab. The macro goes to a 3rd party app, extracts a report, and exports it into a new workbook "Raw_Report" (sheet 1). Macro then copies that Raw data into the "Master_Report_2021" on the "Raw" sheet; then closes the "Raw_Report". Next, it creates a new sheet with today's date (mm-dd) and copies specific colomns from "Raw" sheet over to that new date tab. On the new date tab (always the 4th tab based on code), I use a VLOOKUP in column F to compare values in column D & G, and based on those values, the VLOOKUP places text in column F based on a table stored on the "Home Page" tab.
This used to be a "Master_Report" but I am now updating to include the year, so we can create a new file each year (adding "_2021"; which the year is now referenced in cell T6 on "Home Page" tab).

Summary Answer to your questions:
1. VBA code is stored in Module 1 of the "Master_Report_2021" and activated by clicking a button on the "Home Page" sheet.
2. VLOOKUP is placed in "Master_Report_2021" workbook, Sheet (4), Column F2:F
3. VLOOKUP compares (2) values on Sheet(4), columns D & G. Then goes to a table on Sheet "Home Page" to determine the output.

Hopefully I explained that in a way that is helpful. If needed, I can include additional code or create a dummy copy of the file to help decipher the code process.

I greatly appreciate your help.

 

[Joe4]

Can't you use the current date to get the year you need, i.e.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[Master_Report_" & Format(Date, "yyyy") & ".xlsm]Home Page'!$BC$50:$BD$343,2,FALSE)"

 

[ExelTime]

Can't you use the current date to get the year you need, i.e.

Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($D2&$G2,'[Master_Report_" & Format(Date, "yyyy") & ".xlsm]Home Page'!$BC$50:$BD$343,2,FALSE)"

I am a fool. I think I was overthinking the entire thing. This worked as needed. Thank you for your help.

 

[Joe4]

I am a fool. I think I was overthinking the entire thing. This worked as needed. Thank you for your help.

No worries! We all have times like this where we can't see the forest for the trees!
Just glad we got it working for you.