ClearContents doesn't always work

[Murman01]

Hi,
Using Excel 2016, I have a yearly workbook that is broken in to months, quarters, and year-to-date worksheets. At the start of each fiscal year (April to March), I want to give the user the ability to clear all of their data entries in the entire workbook at the click of a single macro-enabled button on the SETUP worksheet. I've also want to give the ability to clear each month or quarter roll-up worksheet's data entry with a macro-enabled button on each worksheet.

The macro for each individual month or quarter roll-up works as expected. The single button on the SETUP worksheet to clear the entire year works on all sheets until it goes to execute the "ClearContents" command for Sheet20 (the chart worksheet). The monthly sheets have different data to be cleared when compared to the quarterly and yearly worksheets; hence, I've used SELECT CASE in Module 1 in Sub ClearEntireYearCells() the "Range("E3:F4"). For some reason, "ClearContents" command doesn't work when "Case "SHEET20"", but yet the same command works on all the other sheets.

I must have spent hours looking for what I'm missing. Any suggestions? I thank you in advance for your help!!

Sheet2 is the setup sheet
Sheet3 to Sheet19 are the monthly and quarterly sheets
Sheet20 is the year-to-date sheet.

Public Sub ClearSingleMonthCells()


    'This will clear a single month's data cells in a specific worksheet, but leave the formulas.


    If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
    On Error Resume Next
            
    With ActiveWorkbook.ActiveSheet
        Range("E3:F4").ClearContents   'clears contents of the header information.
        Range("C9:C9").ClearContents   'clears contents of # paid hours in month.
        Range("B12:C23").ClearContents   'clears monthly data but leaves the formulas.
        Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
        Range("A29:I46").ClearContents  'clears the comments section.
    End With


    MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
    
End Sub





Public Sub ClearRollupMonthCells()


    'This will clear the roll-up sheet's data cells in a specific worksheet, but leave the formulas.


    If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
    On Error Resume Next
            
    With ActiveWorkbook.ActiveSheet
        Range("E3:F4").ClearContents   'clears contents of the header information.
        Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
        Range("A29:I46").ClearContents  'clears the comments section.
    End With


    MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
    
End Sub




Public Sub ClearChartCells()


    'This will clear all monthly data cells in SHEET20.


    If MsgBox("This will clear all the data from the " & ActiveSheet.Name & " worksheet and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
    On Error Resume Next
            
    With ActiveWorkbook.ActiveSheet
        Range("E3:F4").ClearContents   'clears contents of the header information.
    End With


    MsgBox "The monthly clear data on the " & vbNewLine & vbNewLine & ActiveSheet.Name & " worksheet" & vbNewLine & vbNewLine & " has been completed."
    
End Sub



Public Sub ClearEntireYearCells()
    'https://www.ozgrid.com/forum/forum/help-forums/excel-general/84813-run-same-vba-macro-code-on-multiple-sheets
    'This will clear all monthly data cells in all worksheets, but leave the formulas.


    If MsgBox("This will clear all the data for the year from the entire workbook and it can't be undone!" & vbNewLine & vbNewLine & "Are you sure you want to continue?", vbExclamation + vbYesNo) = vbNo Then Exit Sub     'verifies with user
    
    On Error Resume Next     'if there are no contents in the cells, the ClearContents will give an error.  This code ignores the error and continues.


    Application.ScreenUpdating = False
     
    Dim sh As Worksheet
     
    On Error Resume Next     'if there are no contents in the cells, the ClearContents will give an error.  This code ignores the error and continues.
   '
   'The following code is to clear the monthly worksheets.
   '
    For Each sh In Worksheets


        Select Case UCase(sh.CodeName)     'SELECT CASE is case sensitive.


            Case "SHEET3", "SHEET4", "SHEET5", "SHEET7", "SHEET8", "SHEET9", "SHEET11", "SHEET12", "SHEET13", "SHEET15", "SHEET16", "SHEET17"
                With sh          'Need the ".Range" when in a WITH statement.
                    'Clearing each single month.
                    .Range("E3:F4").ClearContents   'clears contents of the header information.
                    .Range("C9:C9").ClearContents   'clears contents of # paid hours in month.
                    .Range("B12:C23").ClearContents   'clears monthly data.
                    .Range("G12:I23").ClearContents   'clears monthly data.
                    .Range("A29:I46").ClearContents  'clears the comments section.
                End With
                
            Case "SHEET6", "SHEET10", "SHEET14", "SHEET18", "SHEET19"
                With sh          'Need the ".Range" when in a WITH statement.
                    'Clearing quarterly and year to date rollups.
                    .Range("E3:F4").ClearContents   'clears contents of the header information.
                    .Range("G12:I23").ClearContents   'clears monthly data but leaves the formulas.
                    .Range("A29:I46").ClearContents  'clears the comments section.
                End With


            Case "SHEET20"
                'Clearing header info on CHART worksheet.
                Range("E3:F4").ClearContents   'clears contents of the header information.
        End Select
    Next sh
    
    Worksheets(2).Activate   'Return to SHEET2
    
    Application.ScreenUpdating = True
    
    MsgBox "The yearly clear data has been completed."
    
End Sub

 

[MARK858]

What happens if you change

For Each sh In Worksheets

to

For Each sh In Sheets

 

[steve the fish]

You need to use a period/dot before the Range (ie .Range).

 

[MARK858]

Actually it needs sh.Range("E3:F4").ClearContents as it is outside the With statement.
The changing of Worksheets to Sheets still stands as a chart sheet is part of the Sheets collection not the Worksheets.

 

[steve the fish]

It must be a worksheet with a chart rather then a chart sheet as they dont have cells to clear.

 

[MARK858]

It must be a worksheet with a chart rather then a chart sheet as they dont have cells to clear.

True, wasn't thinking when the OP stated it was a "chart worksheet" thanks for pointing it out.

 

[Macropod]

Cross-posted at: https://www.excelguru.ca/forums/showthread.php?9947-ClearContents-doesn-t-always-work
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Murman01]

Sorry about the cross-posting. As you can see, I don't post often so didn't know the rules very well. I won't make this mistake again.

Thank you very much, MARK858. Adding the sh. before Range("E3:E4") statement solved it. (how do I mark this thread as being [SOLVED]?)

Yes, steve the fish, it is a worksheet with a chart rather then a chart sheet.

I appreciate everyone's assistance on this. Cheers!

 

[MARK858]

You don't mark threads as solved on this forum, you just say thank you (which you have already done