How do I Vlookup into another workbook and search for a date in a sheet to copy 3 cells.

I have a source workbook that has 52 weeks of the year.(one sheet for each week, plus a couple at the beginning) Each sheet has the same table and same cells.
Every week new data is inputted into the table. At the end of the table it totals up all three cells.
The data is... A number, A goal, and a percentage of the number to that goal.

Help 01.png

Week one of 52 weeks will start on sheet#3 (third sheet from left) Week 2 will be on Sheet #4 and so on.

The Dashboard workbook, the user will enter a date in the "fill date here" cell.
The Vba will go to Source workbook, and find and open the sheet for that week of the date, and copy the date (3 cells example E4, E5, E6).
It will then paste these three cell into the dashboard workbook sheet. At the same time it will also copy the Week Total at the end of the table ( 3 cells K4,K5,K6)
It will also paste this into the dashboard workbook sheet.

Help 02.png

Does this make sense?
Thanks

Hi John,
Thanks so much for your help?? This works Awesome..
I asked the same question??..( why not on one sheet? )
This "Dashboard Workbook" will pull data from three different workbooks when we are finished.
This "Source Workbook" is just the first one.

In this source book, there is a lot more going on at the bottom, I stripped it down to make it easier to work on. But yah, it would be nice if it was all in one workbook.

How on earth does your workbook reference the source workbook...?
I imagine both workbooks need to be in the same folder What if the source file name is different?
I guess I could have a macro to open source book when I want to run the dashboard. That's easy.

Is it hard to open another work book then switch back to the workbook we are working on ( Dashboard ), so that source is open and ready?

Thanks so much.

Chris

VBA, if you like
Assuming both workbooks are in the same folder.
Run when you change the date in B3.
1) To FIll In sheet code module

Please Login or Register  to view this content.

2) To a standard code module

Please Login or Register  to view this content.