Certain parts of code to clear cells is not working

[dsrt16]

I have a reset button on a user forum to reset the workbook to its original state. Certain parts of the code do not work, and I am not sure why. I have placed the two sections of the code that doesn't work in comments.



FIRST half of the code. Problem #1: It clears everything on sheet1, but then the debugger runs when it gets to sheet2, and gives a range error.

Private Sub CommandButton1_Click()
    Sheet1.Range("B5:C34").Select
    Selection.ClearContents
    Sheet1.Range("E10, E12, E14, E16").Select
    Selection.ClearContents
    Sheet1.Range("B5").Select

    'Sheet2.Range("C5:E12").Select
    'Selection.ClearContents
    'Sheet2.Range("C5").Select

Second half of the code. Problem #2: It will place the formula on sheets 3-32, but it will not clear the contents in the cells.

    Dim i As Integer

    For i = 3 To 32
        Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"
        'Sheets(i).Range("C8:C29").Select
        'Selection.ClearContents
    Next i
    
    Unload Me
End Sub

 

[Norie]

Try this:

 Sheet1.Range("B5:C34").ClearContents
 Sheet1.Range("E10, E12, E14, E16").ClearContents
    
 Sheet2.Range("C5:E12").ClearContents

and this.

    Dim i As Integer
    For i = 3 To 32
        Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"
        Sheets(i).Range("C8:C29").ClearContents
    Next i
    
    Unload Me

 

[machopicho]

Private Sub CommandButton1_Click()     
Sheets(1).Range("B5:C34").ClearContents     
Sheets(1).Range("E10, E12, E14, E16").ClearContents     
Sheets(1).Range("B5").Select      
Sheet(2).Range("C5:E12").ClearContents     
Sheet(2).Range("C5").Select

Dim i As Integer      
For i = 3 To 32        
Sheets(i).Range("D40").FormulaR1C1 = "='Weekly Setup'!R2C2"         
Sheets(i).Range("C8:C29").Select        
Selection.ClearContents    
Next i          
Unload Me
End Sub

 

[dsrt16]

Thanks Norie that worked. I am sure machopicho's code would have worked too.

I have always done .select and then selection.clearcontents, which I clearly don't need to. But I am curious why the .select and selection.clearcontents would would for sheet1 and then it would have a problem on sheet2.

Norie, your code is just less wording, but it is saying the same thing. Because I am learning, I want to know why it didn't work the way I had it.

 

[Norie]

To tell you the truth I don't really know, or more accurately can't explain very well.

It's something to do with not being able to select a worksheet and range at the same time unless the worksheet is active.

So if Sheet1 is active, this is fine,

Sheet1.Range("A1:A10").Select
Selection.ClearContents

but this won't work because Sheet2 isn't the active sheet.

Sheet2.Range("Z1:X100000").Select

Hope that might make it clearer, but it probably won't.

As for the code I posted saying the same thing, sort of but not quite.

 

[dsrt16]

That makes perfect sense actually. Thank you!