+ Reply to Thread
Results 1 to 4 of 4

Run macro on specific sheets

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Costa Rica
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    7

    Lightbulb Run macro on specific sheets

    Hello, I have this code which formats specific cells on specific sheets, it works fine but when I use the Option Explicit argument if does not compile. How can I rewrite it so it compiles with Option Explicit? - Thanks.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2011
    Location
    Mississauga, CANADA
    MS-Off Ver
    Excel 2010
    Posts
    503

    Re: Run macro on specific sheets

    Use this:

    Please Login or Register  to view this content.
    Last edited by Kelshaer; 08-16-2012 at 04:19 PM.
    Regards,
    Khaled Elshaer
    www.BIMcentre.com

    Remember To Do the Following....
    1. Thank those who have helped you by clicking the Star below their post.
    2. Mark your post SOLVED if it has been answered satisfactorily:
    • Select Thread Tools (on top of your 1st post)
    • Select Mark this thread as Solved

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Costa Rica
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    7

    Re: Run macro on specific sheets

    Khaled it works! I knew I had to declare Sheetlist I just did not know which option to use (Variant) and in one of my tries I had i as Integer but since the first was not declared VBA would not compile. Thanks.

    Now here is another challenge I tried the code it compiles an runs fine but it return and error if one of the sheets is not found i.e.: I have Hoja1 but I don't have Sheet1. Sheet3 exists but the format would not change it as when it gets to the second sheet on the sheetlist it gives me the error message instead of probing for the next sheet name. How can we avoid this? What I am trying to do is have the same code run on Sheet1 OR Hoja1 OR Sheet3, etc. What's your suggestion on that?

  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: Run macro on specific sheets

    Using the macro offered, we just need to test if the sheets exist before we try to act on them, so list all the possible sheet variations you want to try in the sheet array:

    Please Login or Register  to view this content.
    _________________
    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!)

+ 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