Thanks to everyone for giving me the suggestions. I was hoping to just use a VBA Macro and a button on Excel to just generate the current month. But I didnt figure that out, however the formulas given did help me out.

I used: =SUM('Day 1:Day 30'!A1)

With some of our daily reports being for 200+ day projects, we will just use the last day (i.e. 195) and subtract the number of days in the month (i.e. 30).

=SUM('Day 165:Day 195'!A1) and get the values needed.