Clear contents on certain sheets

[Crewchief521]

I have a workbook that has about 500 sheets (all different names, but end with the same letter) , I want to create 9 buttons to clear the same cells in each sheet but choose only certain ones for each button. For instance, button 1 will clear about 80 sheets, button 2 will clear about 40 and so on.

Thanks in advance.
Denny

 

[Yongle]

With list of sheets for each button maintained in sheet "Lists"
- column A for button1
- column B for button2 etc

You could use something like this to clear the ranges ...

Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("A3:B11,E7:F14,G17:O18").ClearContents
    Next
End Sub

And call it like this from each button ....

Sub Button[COLOR=#ff0000]1[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp)))
End Sub

 

[Crewchief521]

With list of sheets for each button maintained in sheet "Lists"
- column A for button1
- column B for button2 etc

You could use something like this to clear the ranges ...

Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("A3:B11,E7:F14,G17:O18").ClearContents
    Next
End Sub

And call it like this from each button ....

Sub Button[COLOR=#ff0000]1[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp)))
End Sub

When I click the button I get the error, wrong number of arguments or invalid property assignment.

 

[Yongle]

Without seeing your code I cannot tell what you have done wrong

Your error is most likely is in this line:

Sheets(cel.Value).Range([COLOR=#ff0000]"[/COLOR]A3:B11[COLOR=#ff0000],[/COLOR]E7:F14[COLOR=#ff0000],[/COLOR]G17:O18[COLOR=#ff0000]"[/COLOR]).ClearContents

It is a single string inside " "
Each range is separated by , (comma)

The error you describe would happen if you tried
Range("A3:B11", "E7:F14", "G17:O18")

If you cannot resolve it yourself, please post that line of code

 

[Crewchief521]

[COLOR=#333333][I]Sub ClearCells(aList As Range)[/I][/COLOR]    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("B4:B5").ClearContents
    Next [COLOR=#333333][I]End Sub[/I][/COLOR]

[COLOR=#333333][I]Sub Button[/I][/COLOR][COLOR=#ff0000][I]1[/I][/COLOR][COLOR=#333333][I]_Click()[/I][/COLOR]    Call ClearCells(Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp)))
End Sub

Sub Button[COLOR=#ff0000]2[/COLOR]_Click()
    Call ClearCells(Sheets("Lists").Range("B1", Sheets("Lists").Range("B" & Rows.Count).End(xlUp))) [COLOR=#333333][I]End Sub[/I][/COLOR]

That is my range on my test sheet i have here at home. i get the error when i click the button and it highlights the Call ClearCells(Sheets....) section.

 

[Crewchief521]

Nevermind i think i found my problem, i forgot the (aList as Range) at the end of my sub

 

[Crewchief521]

Sub ClearCells(aList As Range)
    Dim cel As Range
    For Each cel In aList
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
    Next
End Sub

That is my code, I get a subscript out of range error on the sheets(cel.value......) line. Any idea why?

 

[Yongle]

I have added to the code so that it hopefully runs without stopping and tells you something

Sub ClearCells(aList As Range)
    Dim cel As Range, msg As String
    For Each cel In aList
        On Error Resume Next
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
        If Err.Number > 0 Then msg = msg & vbCr & cel.Value
        On Error GoTo 0
    Next
    MsgBox msg
End Sub

My immediate guess would be a typo in one or more sheet names in your list (ie VBA is choking on a non-existent sheet name)
If so, run the amended code the message box will tell you all you need to know

 

[Crewchief521]

I have added to the code so that it hopefully runs without stopping and tells you something

Sub ClearCells(aList As Range)
    Dim cel As Range, msg As String
    For Each cel In aList
        On Error Resume Next
        Sheets(cel.Value).Range("I3,B7:B10,D7:D10,G7:G10,I7:I10,I13,I15,B19:B20,B13:B16,D13:D16,F19:F20,I19,I21").ClearContents
        If Err.Number > 0 Then msg = msg & vbCr & cel.Value
        On Error GoTo 0
    Next
    MsgBox msg
End Sub

My immediate guess would be a typo in one or more sheet names in your list (ie VBA is choking on a non-existent sheet name)
If so, run the amended code the message box will tell you all you need to know

It runs but the message box is blank. I went thru and edited my table to not be longer than needed, to remove blanks.

 

[Yongle]

Why have blank cells in a list of sheets? If the cell values are blank then they are listed in the message box but are invisible! My message box idea was not robust enough
Hopefully they are now all removed
You could add a nonsense name at the bottom to see if it is picked up

To pick up everything including blank cells
Amend the message string to this and the cell address will show

msg = msg & vbCr & cel[COLOR=#ff0000].Address(0,0)[/COLOR] & " " & cel.Value

If the message box is still empt,y then you have eliminated your problem and can prove it by putting an apostrophe in front of On Error Resume Next (I would then re-instate the line to help if you have a mismatch in the future)