Running a macro based on a list

[oam]

I have a workbook with over a hundred sheets and at times the total count and the names of the sheets change so I need away to run a clear macro (macro1) on each sheet based on a list of the sheets.

Hope this makes sense

[Paul_Hossler]

Hope this makes sense


Sorry, no

In your workbook you have a worksheet with a list of worksheets also in the WB and you want to clear the data from any WS in that list?

Define 'Clear' -- make it completely blank like a new one?

A macro to read a list on Worksheets ("ClearThese") ...

AAAA
BBBB
CCCC
GGGG
KKKK

could delete AAAA and then add a new one named AAAA, etc.

[oam]

Sorry for the misunderstanding, Macro1 clears the data from the completed cells

[Paul_Hossler]

Macro1 clears the data from the completed cells

What cells are completed?

Same on every sheet?

Are the completed cells to be cleared the same addresses (B2, C4, Z5, etc.) on all sheets?

Can you post a small sample WB?

[oam]

Because of the information I can’t post the workbook but the same cells are cleared on every sheet and uses the same macro to clear the data.




Sub Macro1()
'Clear Sheet
Range("B2:B16").Select
Selection.ClearContents
Range("H2:H16").Select
Selection.ClearContents
Range("J2:J16").Select
Selection.ClearContents
Range("B21:B35").Select
Selection.ClearContents
Range("I21:I35").Select
Selection.ClearContents
Range("K21:K35").Select
Selection.ClearContents
Range("B2").Select
End Sub

[Paul_Hossler]

Well, this will clear those cells for every worksheet in the workbook if that's what you're looking to do

It makes assumption - sheet not protected, etc.



Sub ClearOnAllWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Range("B2:B16").ClearContents
.Range("H2:H16").ClearContents
.Range("J2:J16").ClearContents
.Range("B21:B35").ClearContents
.Range("I21:I35").ClearContents
.Range("K21:K35").ClearContents
.Range("B2").ClearContents
End With
Next
End Sub

[oam]

There are some worksheets I do not want cleared and that is why I was basing the clearing on the worksheet list. The only worksheets i need cleared are the the ones listed on the worksheet listing.

[Paul_Hossler]

The only worksheets i need cleared are the ones listed on the worksheet listing.

Where's the list and what does it look like?

Making another assumption that the list of sheet names to clear is in Column A of a sheet called Master

No error checking



Option Explicit
Sub ClearSomeWorksheets()
Dim wsList As Worksheet
Dim rList As Range, rSheetToClear As Range

Set wsList = ThisWorkbook.Worksheets("Master")
Set rList = wsList.Cells(1, 1).CurrentRegion.Columns(1)

For Each rSheetToClear In rList.Cells
With ThisWorkbook.Worksheets(rSheetToClear.Value)
.Range("B2:B16").ClearContents
.Range("H2:H16").ClearContents
.Range("J2:J16").ClearContents
.Range("B21:B35").ClearContents
.Range("I21:I35").ClearContents
.Range("K21:K35").ClearContents
.Range("B2").ClearContents
End With
Next
End Sub

[oam]

Paul_Hossler,

Thank you for the code, it works

Thank you for all your help.