Daily Totals_Calculate Daily and Monthly Totals_Calculate Monthly Totals

I routinely use the attached format which has daily totals and create a 2nd worksheet to sum the daily totals to get a monthly total and compare the daily total to monthly total. I then have to create a 3rd worksheet which has only the monthly totals with the ending date of the month. Not all months have 31 days.

Can anyone help me to write a macro which will do this automatically using the data in the 1st worksheet. This is very tedious work and I know there is an easier solution.

Thanks,

Jim15

You should be using the SUBTOTAL function on Daily Production sheet.
Use this macro to calc the subtotals and copy to a new sheet.
(unable to upload attachment! )

edit 2/22/12:

Option Explicit

Sub Macro1()
    Sheets("DAILY PRODUCTION").Select
    Range("E2").Select
    
    Selection.Subtotal _
        GroupBy:=5, _
        Function:=xlSum, _
        TotalList:=Array(2, 3, 4), _
        Replace:=True, _
        PageBreaks:=False, _
        SummaryBelowData:=True
        
    'hide all rows but the subtotals
    ActiveSheet.Outline.ShowLevels rowlevels:=1
    ActiveSheet.Outline.ShowLevels rowlevels:=2
    
    'copy the visible subtotal rows and paste to a new sheet
    ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy
    ActiveSheet.Outline.ShowLevels rowlevels:=3
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Monthly Totals " & Sheets.Count - 1
    ActiveSheet.Paste
    ActiveSheet.Columns(1).EntireColumn.Delete
    ActiveSheet.Range("A1").Select
    
End Sub