Clear worksheets from list

[oam]

I have a macro that creates worksheets from a Master sheet based on a list of items. The macro deletes all the sheets and adds them back based on the list in the Items worksheet. Employees use these sheets to track inventory on a daily basis and at the start of each day all the sheets must be clear. Because the list of items changes, I have to go into the code to update the macro to allow the sheets to be cleared based on the name of the worksheet tab.
What I need to know, is there a way that I can have the “Clear Sheet” macro select and clear each sheet based on a list of items located on the Items worksheet? Below is an example (not all of the lines are included) of the code I am currently using and as you can see I have to select the sheet based on the item number and then call Macro1 to clear the sheet.

Thank you for any help.

Sub Macro2()
'
'Clear ALL worksheets
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
Sheets("Item 19567").Select
Call Macro1
Sheets("Table of Contents").Select
Range("B2").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True 

End Sub

[jolivanes]

If the sheet names are in column B, starting at cell B1, and these sheets do exist in your workbook and the spelling is exactly the same.

Sub Clear_All_Sheets()
    Dim lr As Long, j As Long
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For j = 1 To lr
        Sheets(Range("B" & j).Value).UsedRange.ClearContents
    Next j
End Sub

Note: No error trapping in case a sheet does not exist or if the spelling is different.

[jolivanes]

Maybe this would be better, just in case.

Sub Clear_All_Sheets()
    Dim lr As Long, j As Long, ws As Worksheet
    lr = Cells(Rows.Count, 2).End(xlUp).Row


    For j = 1 To lr
    On Error Resume Next


    Set ws = Sheets(Range("B" & j).Value)
    If Err.Number <> 0 Then
        MsgBox "Worksheet " & Range("B" & j).Value & " does not exist"
    Else
        Sheets(Range("B" & j).Value).UsedRange.ClearContents
    End If
    Next j
End Sub

[oam]

jolivanes, Thank you for your reply. I am unsure how to utilize this code to allow me to run Macro1 after the sheet is selected from a list on the Items worksheet. I need the code to select or make active a worksheet from the list of item names from the Items worksheet and run Macro1 which clears the each sheet. Then I need it to select the next worksheet from the list and run Macro1 and so on...

I hope this clear and thank you for your help.

[jolivanes]

So you have a sheet named "Items". In one of the columns are the worksheet names that need clearing? Which Column?
What does "list of item names" mean? Are that the names of the worksheets? Where is that list? In which Column?
The code in Post #3 works from the sheet that is open and has worksheet names that need to be cleared in Column B.
So if, for instance, you have a sheet called "Items" and in Column B, starting at the first Row (Cell B1), you have a bunch of sheet names, it'll clear all the sheets in that range.
Is this not what you want?

[oam]

Yes! I have a sheet named Items and the list is located in column D for 140 rows (one worksheet per item for a total of 140 worksheets) and the list is also used to recreate the tabs using the Master sheet as a template when I need to recreate the worksheets and the Table of Contents. The code needs to active or select each sheet in the list and run Macro1 which clears the same selected cells in each sheet. I can not have the code clear all cell contatined within the sheet due to formulas, only the cell containing data entered by the employees which Macro1 addresses this issue.

Hope this helps.

[jolivanes]

It sounds like if you change the code in post #3 with this, it should do what you described. You can run it from any sheet.

Sub Clear_All_Sheets()
    Dim lr As Long, j As Long
    lr = Sheets("Items").Cells(Rows.Count, 4).End(xlUp).Row
     
     For j = 1 To lr    '<---- The sheet names to be cleared are in Sheet Items from cell D1 on down. If they start in cell D2, change the 1 to a 2.
        On Error Resume Next    '<---- If a Sheet does not exist or the spelling is wrong, code continues
         
            With Sheets("Items")
                Sheets(.Range("D" & j).Value).Range("A1:B1").ClearContents    '<----- Change the "A1:B1" to the range that you want cleared.
            End With
    Next j
End Sub

If the range to be cleared is not a single block of cells, it needs to be changed. Let us know the range(s) to be cleared.
Try that on a copy of your workbook
There is a faster way if all the ranges to be cleared are the same.

[oam]

Works Great! Thank you for all your help. After I added the cell ranges that Macro1 cleared to the below line of code it worked perfectly.

Thank you again
Sheets(.Range("D" & j).Value).Range("B2:B16,H2:H16,J2:J16,B21:B35,I21:I35,K21:K35").ClearConten ts