reference column date (yesterday) to get Month(Date) for copy paste

Hello I'm new and confused.

I have been working on my first VBA programming project for the past 2 weeks and now I'm stumped. The workbook will have data pushed to Sheet1. Depending on the date (yesterday) it would then need to copy and paste yesterday's column to the next available column on the correct month's spreadsheet.
Currently I have the info copy and pasting the active column info to the months spreadsheet but it is using today's date to figure the month.

My dates are in (B2:H2) and Sheet2 - Sheet13 are the months January - December although I have just left them named Sheet2-Sheet3 for simplicity. Once they are pasted to the correct months sheet, the MTD is calculated.

When I try using anything other than Month(Date) to determine the correct spreadsheet to paste to, it fails. I have tried Today - 1, Date - 1 to get yesterday's date but I don't have any reference to the range of dates B2:H2

The code that I need help with is:

Please Login or Register  to view this content.

The beginning code in my workbook is this but I believe I have a lot of useless info there as the cell range for my dates doesn't seem to do anything.

Please Login or Register  to view this content.

And I tried to upload my workbook but I'm getting the error "This asset is not currently being used by any content that you have access to."
I'm thrilled that I have the macro running at all but if I could get help with this last piece to have it fully automated it would be a tremendous help.

Thank you!

It looks like the workbook attached after all. If you run the macro today it will erase all of the data. I have it set to clear.contents on sheet1 if Today-1 = vbSunday so if anyone out there is looking to help please change that before running it.

Thank you again!