Clearing worksheets

PMRetired2012

New Member
Joined
Aug 6, 2019
Messages
45
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,495
Office Version
2013
Platform
Windows
err
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,495
Office Version
2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,922
Messages
5,411,265
Members
403,353
Latest member
ecboy1605

This Week's Hot Topics

Top