VBA Code for selecting multiple sheets

    This is my first post if done correctly this time. I guess I have to visit to find a response to receive in my inbox instead of receiving through an email or notification.
    I have a workbook with 7 sheets (each for a day of the week) and other sheets as well.
    I want to reset validations in a ranges of cells and clear other cells in each of the seven sheets only, ALL at one time.
    I have a working code that works via a macro button on each of the 7 sheets (relabeled "Su", "M", "T", "W", "Th", "F", & "Sa"), but they only reset the sheet they are on. It is the same code on each sheet if I understand correctly.
    I have renamed each of the sheets in the properties from the "sheet1" to "Su", etcetera. I will enclose a screenshot of the properties and the code and then upload the code as text in





    Thank you for any help you can give.
    Tom

    Re: VBA Code for selecting multiple sheets


    You can loop through an array of sheet names setting the values for each - not too willing to spend time on an exact answer because of your emphasised ALL at one time' but you could do something like

    Re: VBA Code for selecting multiple sheets


    You may define all the possible sheets withing an Array() statement and try the following code so if the sheet exists in the workbook it will be reset.


    Regards.
    sktneer

    Re: VBA Code for selecting multiple sheets


    [FONT="Verdana"]Thank you bothvery much. I am learning as you try to help me. I have abook I am trying to learn from, but it is difficult trying to jump in themiddle of the book (VBA and Macros: Microsoft ® Excel ® 2010 Bill Jelen TracySyrstad) to find how to get something done.


    The code did not work for me, however I might not have applied itcorrectly. The code ran as before I added your new portion: Eachsheet only, cleared as I selected each Macro button I had placed on eachsheet, instead of each Macro button on each sheet clearing all sheets. Atfirst I put the code on only one sheet and as I stated only that sheetreset. Then I place the code on all sheets and only each sheet would clearas I pressed the macro button on that sheet. The task completed messageshowed up on each page (nice) as I selected each sheet macro separately. I did notice that after I entered this message code, when I selected the Macrobutton for each sheet I could not select another sheet until Iresponded to the message "Task Completed." (I ran the codewithout the message at first.)
    ...
    I stepped into the code and it continued, repeating each trip through the codeas I continued for 10 loops, so at least I know it wasn't seeing Su through Sa,or stopping after 1 trip through the code. Anything else I might trying... and thank you again.[/FONT]

    Re: VBA Code for selecting multiple sheets


    Few points:


    1) Where did you place that code, on Sheet Module? This code is supposed to reside on a Standard Module. Insert a New Module and place this code. And then assign this code to the each button on the sheets.


    2) Make sure the Sheet Names are exactly similar to one defined withing the Array(), no leading or trailing spaces.


    If this doesn't help, please upload the workbook in question so that we can find out what's wrong with the code setup.

    Regards.
    sktneer

    Re: VBA Code for selecting multiple sheets


    The code as written in post 3 won't work because the With block isn't doing anything. Try something like this:

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: VBA Code for selecting multiple sheets


    Good catch rory!
    I missed to notice pretty small thing. Just copy & pasted the op's code altogether without any change. My bad.


    Thanks for pointing this out. :)

    Regards.
    sktneer

    Re: VBA Code for selecting multiple sheets


    We've all done it! :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login