VBA - Show cell comments for a specific range of cells based on date, in a table format

I have two worksheets. One is the source worksheet, where all my data and comments are added, the other is a Report worksheet, which takes the necessary subset of data it needs (there are a few different report workbooks, all taking data, this data changes each week according to resourcing requirements). The Source worksheet has Names down the B column, and Dates across Row 4, with positions allocated in the table. Sometimes there are comments against those positions.

I need to be able to look at a weeks worth of data, and take any comments against these cells to give a print out of them in table format. The table needs to have the format: Date, Name, Cell Value (aka resource position), Cell Comment. Not the typical 'Address' command as this is not helpful. The week in question is determined by the dates which are input at the top of the Report worksheet, these are entered as a formula (i.e =TODAY()+7-WEEKDAY(TODAY()+7-2) for the Monday and then every other day as a plus 1). I'm not sure if this will cause me issues.

I'm (highly) inexperienced at VBAing and have come up with a (highly fail) code as below, with heaps of bugs (most likely) which I need help fixing up to return the above result. I've put in comments to try explain what I'm attempting to do with each row as it's probably not obvious.

Any help would be appreciated, especially with explanations as to why as I really am trying to understand and learn it!

Please Login or Register  to view this content.

Apologies, I thought it would be better to attempt it first and had tried to explain what I was doing under each line of code.

The row that specifies the dates to be included is Row 3 of the Active worksheet, from cells D3 to J3. The row with the dates which are being matched against is row 4 of the Source Worksheet (called Prod Planner).