"clear" button not working

[niyrho]

Trying to set up a button to clear certain ranges of cell over 7 different sheets. Not sure what's wrong with my code. Someone will probably laugh at me, but here it is:


Private Sub ClearButton1_Click()
On Error GoTo Exits
cancel = True
Application.EnableEvents = False
With ActiveSheet
Worksheets("Monday").Range("monone").ClearContents
Worksheets("Monday").Range("montwo").ClearContents
Worksheets("Tuesday").Range("tueone").ClearContents
Worksheets("Tuesday").Range("tuetwo").ClearContents
Worksheets("Wednesday").Range("wedone").ClearContents
Worksheets("Wednesday").Range("wedtwo").ClearContents
Worksheets("Thursday").Range("thurone").ClearContents
Worksheets("Thursday").Range("thurtwo").ClearContents
Worksheets("Friday").Range("frione").ClearContents
Worksheets("Friday").Range("fritwo").ClearContents
Worksheets("Saterday").Range("satone").ClearContents
Worksheets("Saterday").Range("sattwo").ClearContents
Worksheets("Sunday").Range("sunone").ClearContents
Worksheets("Sunday").Range("suntwo").ClearContents
End With
Exits:
Application.EnableEvents = True
End Sub



I just picked apart another code that was working. Did fine with the print and save functions. This is the only one I can't get.

[jproffer]

Worksheets("Sunday").Range("suntwo").ClearContents

The "Range" in your code should be the cell address.
What is "suntwo", an entire column? If so then

Range("A:A").ClearContents or whatever column it is.

Either way the only way "suntwo" would work is if you set it as a variable and then the code would be

Range(suntwo).ClearContents


with no quotation marks

[niyrho]

The ("suntwo") is a named range. There are so many of them because it wouldn't let me make a named range that long. Can't just clear the whole page because it will delete some of my codes too.

[niyrho]

Changing that didn't work for me.

I got 8 sheets. On one sheet is a clear button. I need that button to clear the cell ranges on the other 7 sheets. I'm sure I'm just not defining the ranges properly. Any ideas anyone?

[CreganTur]

There's nothing wrong with using named ranges in the VBA Range method.

The problem may be the fact that you're refering to the worksheet's name and not it's code name. Look at the list of worksheets in your project explorer in VBE- they still show Sheet1, Sheet2, etc. This is its code name. Using the code name may help clear up this problem... and it can head off future problems (say if you rename a worksheet).

[david000]

Private Sub test()
Dim wks As Workbook
Dim Sh As Worksheet
Set wks = ThisWorkbook

For Each Sh In wks.Worksheets
Select Case Sh.Name
Case "Monday"
Sh.Range("monone").ClearContents
Sh.Range("montwo").ClearContents
Case "Tuesday"
Sh.Range("tueone").ClearContents
Sh.Range("tuetwo").ClearContents
Case "Wednesday"
Sh.Range("wedone").ClearContents
Sh.Range("wedtwo").ClearContents
Case "Thursday"
Sh.Range("thurone").ClearContents
Sh.Range("thurtwo").ClearContents
Case "Friday"
Sh.Range("frione").ClearContents
Sh.Range("fritwo").ClearContents
Case "Saturday"
Sh.Range("satone").ClearContents
Sh.Range("sattwo").ClearContents
Case "Sunday"
Sh.Range("sunone").ClearContents
Sh.Range("suntwo").ClearContents
End Select
Next Sh
End Sub

[Bob Phillips]

Why not create local names for each worksheet, no need for to test at all then.

[david000]

Why not create local names for each worksheet, no need for to test at all then.


Good point

Sub test2()
Dim MON, TUE, WED, THU, FRI, SAT, SUN As Range

Set MON = Range("Monday!monone,Monday!montwo")
Set TUE = Range("Tuesday!tueone,Tuesday!tuetwo")
Set WED = Range("Wednesday!wedone,Wednesday!wedtwo")
Set THU = Range("Thursday!thurone,Thursday!thurtwo")
Set FRI = Range("Friday!frione,Friday!fritwo")
Set SAT = Range("Saturday!satone,Saturday!sattwo")
Set SUN = Range("Sunday!sunone,Sunday!suntwo")

MON.ClearContents
TUE.ClearContents
WED.ClearContents
THU.ClearContents
FRI.ClearContents
SAT.ClearContents
SUN.ClearContents

End Sub

[Bob Phillips]

That is not what I meant, I meant create a name called DayOne and DayTwo on each sheet and then use



For Each Sh In wks.Worksheets
Sh.Range("DayOne").ClearContents
Sh.Range("DayTwo").ClearContents
Next Sh
End Sub

[niyrho]

david000: I tried your code and it gave me a run-time error '1004: method 'range' of object'_worksheet' failed

xld: I'm not sure if I'm putting your code in right. Don't really understand it. I'm pretty new to vba.

[david000]

Oh - I surprised nothing worked for you niyrho I tested everything on a workbook with a sheet named Monday and Tuesday - but maybe there's something going on in your workbook that I am unaware of that would botch the results. Maybe post a test version to the forum for debugging.

[niyrho]

Maybe I have the code in the wrong place. I'm putting it in sheet 3(time sheet). Thats where the button is. Where do you have it?

[david000]

Check the spelling of your worksheets...i.e. "Saterday" for Saturday. And what XLD is referring to is the "scope" of a range name. He suggested that you go through and simply use two names "DayOne" and '"DayTwo". How you do that; is first read the help files on the scope of a range name then you can name the range like; Monday!DayOne etc for 'each sheet. You'll notice that after you name the range and press enter the sheet name will disappear. Thus, it is (scoped) referring to that sheet only and can be reference again on the next sheet like this, Tuesday!DayOne. It is admittedly confusing and time consuming. And it's nothing that we (I) can do with out an attachment.



Private Sub ClearButton1_Click()


'Put the code here without the Sub or End Subs that I wrote (use the one you 'have before)

End Sub

[niyrho]

I just got it to work like:

application.goto referance:="monone"
selection.clearcontents

over and over and over again

It's prolly needlessly long, but it works.

Thanks for the help guys.