Clear Contents of specific worksheets and ranges

[Learn_VBA]

Sub Clear()
Application.DisplayAlerts = True
Dim IA As Workbook:
Dim ES As Worksheet
Dim InputSheet As Worksheet
Dim Competition As Worksheet
Dim Demographics As Worksheet
Dim AlteryxOutput As Worksheet
Dim Impact As Range
Set IA = ThisWorkbook
Set ES = IA.Sheets("Executive Summary")
Set Competiton = IA.Sheets("Competition")
Set AlteryxOutput = IA.Sheets("Alteryx Output")
Set InputSheet = IA.Sheets("Input Sheet")
Set InputValues = ES.Range("B1:B14")
Set Impact = ES.Range("L11:M13")
InputSheet.ClearContents
Competition.ClearContents
AlteryxOutput.ClearContents
End Sub

 

[Joe4]

A few things:

- You have a typo:

[COLOR=#333333]Set [/COLOR][COLOR=#ff0000]Competiton[/COLOR][COLOR=#333333] = IA.Sheets("Competition")[/COLOR]

If you use "Option Explicit" at the top of all your modules, it will catch these kind of typos and alert you

- You have not declared "InputValues"

- I think you may need to Activate each sheet before you clear its contents

- I am not sure why you are setting ranges when you aren't using them at all in this procedure.

Here is a version of your code that clears all contents on those three tabs:

Sub MyClear()
    
    Application.DisplayAlerts = True

    Dim IA As Workbook:
    Dim ES As Worksheet
    Dim InputSheet As Worksheet
    Dim Competition As Worksheet
    Dim Demographics As Worksheet
    Dim AlteryxOutput As Worksheet
    Dim InputValues As Range
    Dim Impact As Range
    
    Set IA = ThisWorkbook
    Set ES = IA.Sheets("Executive Summary")
    Set Competition = IA.Sheets("Competition")
    Set AlteryxOutput = IA.Sheets("Alteryx Output")
    Set InputSheet = IA.Sheets("Input Sheet")
    
    Set InputValues = ES.Range("B1:B14")
    Set Impact = ES.Range("L11:M13")
    
    InputSheet.Activate
    Cells.ClearContents
    
    Competition.Activate
    Cells.ClearContents
    
    AlteryxOutput.Activate
    Cells.ClearContents
    
End Sub

 

[Learn_VBA]

Thank you.

 

[Joe4]

You are welcome.