+ Reply to Thread
Results 1 to 8 of 8

Loop through worksheets which are defined in a range of cells

  1. #1
    Registered User
    Join Date
    01-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    30

    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!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 11-22-2010 at 07:32 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    30

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

    Quote Originally Posted by JBeaucaire View Post
    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

    Please Login or Register  to view this content.

    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!
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    This should get you started:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    30

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

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

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.

  7. #7
    Registered User
    Join Date
    01-28-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    30

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

    Quote Originally Posted by JBeaucaire View Post
    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

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    If your sorting code is missing the worksheet references then it will keep sorting the activesheet instead of the target worksheet.

    If you're code is:
    Please Login or Register  to view this content.
    ...then you add this to make "sheet aware" so the copy command acts on the target sheet:
    Please Login or Register  to view this content.

    You'll need to adjust your sort code the same way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1