VBA/Macro pull data from a new weekly excel

I am just starting off on my excel journey, but I just started playing around with Macros this week to automate my weekly report. I have 2 separate excel files that I get every week. On one of them, I have to re-format to the same format every week, pull in data from the other excel I get weekly, and then send out to the same people every week. I figured out how to do the formatting and the vlookup (for the current week's version) through macros, but I am inquiring about how to get that vlookup to pull from the updated report (that gets saved weekly to the same location, with the same name except for the date) by using a macro and then to be able to send that report to the same people every week.

Any help would be greatly appreciated!!

ndenny

Hi and welcome
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.