Here's my situation:
I've got two spreadsheets. One has columns consisting of 7 date headers (ranging from Friday to Thursday) with the far left column listing several representatives. There are number counts below the dates which need to be captured from spreadsheet 2.
The problem:
Spreadsheet 2 is linked to an Access database and consists of the far left column of representatives, however, the date columns/headers constantly change because sometimes workers come in on the weekend, sometimes they do not. SO, there may not always be 7 days like spreadsheet one. Keeping in mind that this is the source that I must use to pull the data from.
What I have:
I currently have a macro in place for columns and rows that are constant.
It seeks out named region in both spreadsheets, copies from one, then pastes just values into the other.
What I need:
Someone to tell me how I can (without being too complex) incorporate into my macro a search to match dates in both spreadsheets by their rows prior and then pull the corresponding data in the below column and row ranges.

I hope this makes sense...
Any help would be tremendously appreciated.

What using the Excel MATCH Function in VBA? To see what it is like, record a macro and setup one cell to use the MATCH function, then see how that looks in VBA.

OTOH, is there specific need to use VBA?