Global list of worksheets for other modules to use

[GeeWhiz7]

Hi folks,

I have a workbook with many worksheets (54) and I am searching for an easier way to clear the cell content on some of them while leaving others untouched prior to running macros that refresh information on the cleared sheets.

Here is a simplified version of what I do currently. Since I don't clear all worksheets, I'm setting each one individually rather than a loop.

Sub ClearIt()
Dim aSh, bSh, cSh, dSh As Worksheet '*********imagine here there are about 30 additional ones

Set aSh = Worksheets("a")
Set bSh = Worksheets("b")
Set cSh = Worksheets("c")
Set dSh = Worksheets("d")

With .aSh
.cells.clear
End With

'...*******repeat for each sheet
End Sub

So its not super efficient and when I add some worksheets I have to go back into a few different modules and add Worksheet variables, Set, With, etc.
I also tried setting an array with all the worksheet names and then looping through the array, but it really doesn't decrease the steps that much.

Is there a way to have a global list of worksheets at the top of all my code that then individual modules can access to do things like .cells.clear, etc?
This would allow me to only have to add new worksheet names in one place.

 

[mumps]

I think that using a userform which lists all the sheets that you could choose from could possibly work for you.

clear the cell content on some of them

By this do you mean clear everything on the chosen sheets? Please clarify.

 

[mikerickson]

You could so something like

Dim sheetNames as Variant, oneSheetName as Variant

SheetNames = Array("Sheet1", "Sheet2", "Sheet5")

For Each oneSheetName in sheetNames
    ThisWorkbook.Sheets(oneSheetName).Cells.Clear
Next oneSheetName

 

[dmt32]

Hi,
Rather than hard code the names, create a list in a worksheet which will be easier to maintain & have your code read from that.

try this update to your code

Sub ClearIt()
    Dim ws      As Worksheet
    Dim wsNames As Variant, wsName As Variant
   
    wsNames = ThisWorkbook.Worksheets("Sheet Names").Range("A1").CurrentRegion.Value
   
    For Each wsName In wsNames
        Set ws = ThisWorkbook.Worksheets(wsName)
       
        'do stuff
        ws.Cells.Clear
       
        Set ws = Nothing
    Next wsName

End Sub

note: no error checking included.

You will need to add all required worksheets to sheet below named "Sheet Names"
This sheet can be hidden.

Book1
	A
1	Sheet1
2	Sheet2
3	Sheet3
4	Sheet4
5	Sheet5
Sheet Names

Dave

 

[mumps]

Click here to download a sample file. Run the macro in Module1.

 

[GeeWhiz7]

You could so something like

Dim sheetNames as Variant, oneSheetName as Variant

SheetNames = Array("Sheet1", "Sheet2", "Sheet5")

For Each oneSheetName in sheetNames
    ThisWorkbook.Sheets(oneSheetName).Cells.Clear
Next oneSheetName

I can say that all the methods proposed worked and to mumps, yes it is to delete everything.
So I can think of uses for all of the examples, but wondering if a variation of mikerickson's approach would work...I can't get it to so far, but

Is there a way to declare the list public and then use it in any subsequent macros?
Maybe that looks like this, but a working version?

Option Explicit

Public SheetNames As Variant = Array("Sheet1","Sheet2","Sheet4")
Public oneSheetName As Variant


Sub ClearIt()

For Each oneSheetName In SheetNames
    ThisWorkbook.Sheets(oneSheetName).Cells.Clear
Next oneSheetName

End Sub

 

[mumps]

Have you tried the sample file I posted in Post#5?

 

[GeeWhiz7]

Have you tried the sample file I posted in Post#5?

I did and it works perfectly. My only catch is that I would have more than 30 check-boxes (of 50 worksheets) to check so would either need to initialize them as checked to start with or hand check them each time. So my thinking was if I put the 30 names in a list (array) in the declarations as public, I could loop using that variable. But, as usual for me, what seems like it might work, isn't working.

 

[dmt32]

So my thinking was if I put the 30 names in a list (array) in the declarations as public, I could loop using that variable. But, as usual for me, what seems like it might work, isn't working.

Did you try the suggestion in #post 4?

Dave

 

[GeeWhiz7]

Did you try the suggestion in #post 4?

Dave

I did and that worked very well too. I was hoping to park it all at the top of my code as public, but i think that having a separate list on a worksheet will work and in the end probably will be clearer to me what is going on 6 months down the line when I am looking back at my code for tweaks. Thank you guys for the solutions! It's always interesting to see how different people approach the solution in a different way.