Speed up code

[Siyanna]

Please please please help me.

This code works fab however it takes ages to run.

Can you please advise how i can speed this up. Please

I have 60 sheets in my workbook

Private Sub CMD1_Click()

Dim LastWsRow As Long
Dim Matchdate As Range
Dim Rng As Range
Dim Matchnum As Long
LastWsRow = Sheets("MET015").Cells(Rows.Count, "B").End(xlUp).Row
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
    
Sheets("DAILY REPORT").Range("C5:AI9,D29:W33,D65:AC69").ClearContents
    
Set Rng = Sheets("MET015").Range("B6:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)
    
    If Matchdate Is Nothing Then
       MsgBox "Enter date between " & Sheets("MET015").Cells(6, "B") & " - " & Sheets("MET015").Cells(LastWsRow, "B"), , "ALERT"
       Exit Sub
    End If
    Matchnum = Matchdate.Row
    
With Sheets("DAILY REPORT")
.Range("D3").Value = UserForm1.CMB1.Value
    If UserForm1.CMB1.Value = "ALL" Then
        
       For r = 1 To 5
         .Cells(r + 4, 3) = .Cells(r + 4, 3) + Sheets("MET015").Cells(r + Matchnum - 1, 2)
       Next r
       
           
        
        For Each ws In Worksheets '...Loop through all sheets
            If ws.Name <> "DAILY SUMMARY" And _
            ws.Name <> "DAILY REPORT" And _
            ws.Name <> "HIGH VOLUME AQ'S" And _
            ws.Name <> "ZERO CONS CHART" And _
            ws.Name <> "HIGH VOLUME AQ'S CHART" And _
            ws.Name <> "CONTACT CHART" And _
            ws.Name <> "BILLING CHART" And _
            ws.Name <> "METER READING CHART" And _
            ws.Name <> "LOOKUP CHART" And _
            ws.Name <> "RAW DATA" And _
            ws.Name <> "INDEX SHEET" Then
                For r = 1 To 5
                    For C = 1 To 32
                        .Cells(r + 4, C + 3) = .Cells(r + 4, C + 3) + ws.Cells(r + Matchnum - 1, C + 2)
                    Next C
                Next r
                
                For r = 1 To 5
                    For C = 1 To 20
                        .Cells(r + 28, C + 3) = .Cells(r + 28, C + 3) + ws.Cells(r + Matchnum - 1, C + 39)
                    Next C
                Next r
                
                For r = 1 To 5
                    For C = 1 To 26
                        .Cells(r + 64, C + 3) = .Cells(r + 64, C + 3) + ws.Cells(r + Matchnum - 1, C + 64)
                    Next C
                Next r
                        
            End If
        Next ws
    Else
        For r = 1 To 5
            For C = 1 To 33
                .Cells(r + 4, C + 2) = .Cells(r + 4, C + 2) + Sheets(UserForm1.CMB1.Value).Cells(r + Matchnum - 1, C + 1)
            Next C
        Next r
        
        For r = 1 To 5
            For C = 1 To 20
                .Cells(r + 28, C + 3) = .Cells(r + 28, C + 3) + Sheets(UserForm1.CMB1.Value).Cells(r + Matchnum - 1, C + 39)
            Next C
        Next r
        
        For r = 1 To 5
            For C = 1 To 26
                .Cells(r + 64, C + 3) = .Cells(r + 64, C + 3) + Sheets(UserForm1.CMB1.Value).Cells(r + Matchnum - 1, C + 64)
            Next C
        Next r
    End If
    
   With .Range("E5:E10,G5:G10,I5:I10,K5:K10,M5:M10,O5:O10,Q5:Q10,S5:S10,U5:U10,W5:W10,Y5:Y10,AA5:AA10,AC5:AC10,AE5:AE10,AG5:AG10,AI5:AI10,AM5:AM10")
        .NumberFormat = "$#,##0.00"
   End With
   
   With .Range("E29:E34,G29:G34,I29:I34,K29:K34,M29:M34,O29:O34,Q29:Q34,S29:S34,U29:U34,W29:W34,AA29:AA34")
        .NumberFormat = "$#,##0.00"
   End With
   
   With .Range("E65:E70,G65:G70,I65:I70,K65:K70,M65:M70,O65:O70,Q65:Q70,S65:S70,U65:U70,W65:W70,Y65:Y70,AA65:AA70,AC65:AC70")
        .NumberFormat = "$#,##0.00"
   End With

    With .Range("D:AM")
    .EntireColumn.AutoFit
    End With
End With
        
 
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
End Sub

 

[Siyanna]

Just to let you know

ws.Name <> "ZERO CONS CHART" And _
ws.Name <> "HIGH VOLUME AQ'S CHART" And _
ws.Name <> "CONTACT CHART" And _
ws.Name <> "BILLING CHART" And _

Are the default chart sheets which i have renamed above. They are not worksheets but chart sheets. Do i need to reference them differently?

 

[Siyanna]

Please help me...watching this code run is taking forever.

Thank you

Gracias

 

[Siyanna]

Any Luck please