Pulling data from another workbook, which name depends on cell

I receive 15 excel files with data every 15 days and I need to consolidate the summaries of all files into an excel, and also retain the previous files that were sent.

The names of the files I receive are in the same format, so they are predictable. I'm trying to write a Macro that pulls data from those files, where the names of the files depend on the value of some cells. Basically I need to do a VLOOKUP to pull data from another workbook that I may or may have not received yet, but I can predict the name of the file. However, I'm having trouble for my code to work.

For the purpose of this example, I'm just running one step of the loop because it's not working with one step. I would also like the macro to skip to the next item in the loop if it doesn't find the file.

Here is my code:

Please Login or Register  to view this content.

I get an runerror type 'Mismatch'

I'm attaching two workbooks to serve as samples. For the first iteration of my code I want the output to be on cell D6 on Sheet1 of workbook "Excel Macro Problem".

It should return the value on cell K27 on sheet "XXX" of workbook "SAN MIGUELITO JUNIO 2020", by doing a VLOOKUP and finding a match between cell C6 on "Excel Macro Problem" with cell J27 on "SAN MIGUELITO JUNIO 2020".

Thanks in advance for any help!

Administrative Note:

Hi and welcome to the forum.

We would very much like to help you with your query, however our rules require that code should include code tags around it.

Please see Forum Rule #2 about code tags. More information about these and other tags can be found here

Since this is your first post I'll add them for you.

In addition please upload your actual workbook so that we can see in context.