VBA to Clear Contents from Multiple Cell Ranges & Multiple Sheets (Not All Sheets)

[TriconKG]

I have a tab labeled "Header" where I'd like to insert a button to "Clear Form" and run the macro to clear the contents of multiple cell ranges on multiple sheets. Sheet names are Line1, Line2, Line3, Line4, Line5. Cell ranges to be deleted from each of the 5 sheets is the same. Can I clear contents with a range of sheets or should I list out each sheet name separately? Do I need to add a line to allow the macro to run from the "Header" tab instead of running from each individual sheet?

 

[Joe4]

Welcome to the Board!

You can loop through all sheets in your workbook, and only run the code on sheets starting with the word "Line", like this:

Sub MySheetClear()

    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
'   Loop through all sheets in workbook
    For Each ws In Worksheets
'       Only run on worksheets starting with "Line..."
        If Left(ws.Name, 4) = "Line" Then
            ws.Activate
'           ENTER YOUR RANGE CLEARING CODE HERE
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub

By the way, it is best to put this code in a General/Standard module, and NOT one of the Sheet modules.

 

[TriconKG]

Joe - Thanks for the assist here. That worked exactly like I needed:

I assigned the macro to a button and now my users can wipe the book clean when they need to start fresh.

As a follow up, could you clarify the "General/Standard Module vs Sheet Module" comment?

Also after running the macro, my cursor is taken to the last sheet in the string. Is there any way to remain on the home tab after running the macro?

 

[Joe4]

As a follow up, could you clarify the "General/Standard Module vs Sheet Module" comment?

Whenever you create your own module, whether intentionally or by recording a macro, it will be in a "General/Standard" module.
These modules are not attached to any sheet, but are, in general, available to ALL sheets.
See here for details on how to create these: Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

Also after running the macro, my cursor is taken to the last sheet in the string. Is there any way to remain on the home tab after running the macro?

Add this line just before the "End Sub" line:

Sheets("Header").Activate