Clearing worksheets

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
123
I have a file that i was wondering if there was a way to write this code differently or easier. The file has 7 worksheets and a button page. i want to clear all worksheet and end up on cell A2 of the button page. Here is the code:

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "MASTER SHEET" And sht.Name <> "BUTTON PAGE" And sht.Name <> "ADDRESS MASTER SHEET" Then 'PRE K-K 1ST-2ND 3RD-4TH 5TH-6TH BOYS 5TH-6TH GIRLS'
sht.Range("A2:P39").Value = ""
End If
Next sht

'Clear Master Sheet Cells
Sheets("MASTER SHEET").Select
Range("A2:S39").Select
Selection.ClearContents
Range("A2").Select

'Clear Address Master Sheet

Sheets("Address MASTER SHEET").Select
Range("A2:G75").Select
Selection.ClearContents
Range("A2").Select

Sheets("BUTTON PAGE").Select
Range("A2").Select



End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi,
sorry about earlier post, I keep having senior moments

maybe this will do what you want

Code:
Sub PMRETIRED()
    Dim sht As Worksheet
    Dim arr As Variant, m As Variant
    Dim strAddress As String
    
    arr = Array("MASTER SHEET", "ADDRESS MASTER SHEET", "BUTTON PAGE")
    For Each sht In ActiveWorkbook.Sheets
        strAddress = "A2:P39"
        m = Application.Match(UCase(sht.Name), arr, 0)
        If Not IsError(m) Then strAddress = Choose(m, "A2:S39", "A2:G75", "A2")
    
        If strAddress = "A2" Then
            sht.Select: sht.Range(strAddress).Select
        Else
            sht.Range(strAddress).ClearContents
        End If
    Next sht
End Sub

Dave
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top