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.
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.
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.
VBA Code:
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.