Thread: Loop through worksheets which are defined in a range of cells

Hello,

I would like to perform the same task (which I have code for already prepared) on various worksheets. This task, however, can not be done unless the worksheet is open, so considering a have 30 worksheets for which I wish the perform the task, I need to create a loop.

I can do this easily enough using an array of worksheets within the VBA code. However, the worksheets I use may change over time. Rather than having my colleagues (who are not necessarily familiar with VBA) try to change the array in the code, I'd like them to be able to just go to some sheet "Control Page" for example, and add to / make changes to a list of sheet names in column A. I'd like the macro to keep looping through sheets based on these cells until there's an empty cell, in which case, the macro ends.

The list of sheets names for the macro to use may grow larger / smaller / stay the same size but different sheets, so finding the empty cell should be the best way to finish?


So, to summarise:
-A macro is needed to loop through worksheets to perform a function
-Ideally would like the worksheets to be defined in column A and keep going until a cell in column A is ""

Thanks you!

Originally Posted by JBeaucaire

1) List only the sheetnames in column A, no titles and nothing listed below them in the same column, just the titles of the sheets to process

2) Create a "range" of the used cells in column A. In this way blanks would be irrelevant, even in the middle of the list, it would still find all the sheetnames and process correctly

3) Loop through each found value and treat it as a worksheet name in your code

Dim wsRNG as Range
Dim wsName as Range

Set wsRNG = Sheets("Control Page").Range("A:A").SpecialCells(xlConstants)

For Each wsName in wsRNG
    Sheets(wsName).Range("A1").Copy
    '...etc.  the rest of your code here, be sure to reference each sheet as shown
Next wsName

Hi, thank you for the code!

I'm experiencing a mismatch error for the line: Sheets(wsName).Range("A1").Copy

I've attached a simple workbook if it might help.

Also, is it possible to add to the code such that if the macro encounters a name in a cell for which no sheets exists, a message box appears and says "Worksheet [name] not found" with options to Skip that worksheet or End the macro?? I added "Dummy" to the A column Range if you need it

Thanks, this would be VERY useful!

It appears to be applying my code on the control page and copying cell "A1" in Sheet4.
Any ideas?
Thanks

Originally Posted by JBeaucaire

Let's see your final code and workbook. You haven't shown any of your own code yet for what you're trying to do. You HAVE to add the Sheets(wsName.Text). reference to the beginning of your commands to make sure they act on that sheet.

The code I'm using is just a sort and some other minor things, I didn't think it would make a difference if I put in the whole code or just Rage("B4").Select.
Unfortunately, I can't attach my final workbook as it contains sensitive information, which is why I created the simple workbook attached above.

Sorry I don't have much VBA experience, so I'm unfamiliar with what you've said about having to have that reference before my commands. I understand the principle, but not sure how to do it.

I've simply copied and pasted your code into the attached workbook above, trying to go through the various sheets I defined in column A so that cells B4 could be selected in each defined sheet.
Once I can do this, can I simply not add more sheet names to the list and change the code from Range Select to my Sorting code?

Sorry I'm trying!! :P
Thanks again mate