Results 1 to 3 of 3

Thread: change active sheet with a list

  1. #1
    Seeker mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    13
    Articles
    0
    Excel Version
    office 365 excel

    change active sheet with a list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a vba script that runs mutiple macros on the same fields in the sheet. I have provided a quick sample. I can not figure out how to make a loop to run the program on a list of sheets that i can input.

    example

    it runs on sheet 1 via

    Worksheets(1).Activate

    I have tried for hours to make a loop so that it can run with numbers that i input in an array. I think an array is the best way , please correct me if i am wrong..

    So the it would look like this


    WorkSheets(N).Activate



    I would have an array with sheet numbers

    N = (1,2,3, etc)

    Then somehow i would have a loop that would run through code and when it ends it would run the loops again on the next sheet 2 and then 3 until all numbers have been ran in the array.

    I hope this makes sense. I can not figure out how to make the code


    Here is my sample data. All the other code works. I just need to run it per sheet that i specify and loop through until all sheets specified are done.
    Attached Files Attached Files
    Last edited by mmoore5553; 2019-05-09 at 10:36 PM. Reason: explain better

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    756
    Articles
    0
    Excel Version
    Excel 2010 64bit
    To answer what you've asked for
    Code:
    N = Array(1, 2, 3)
    For i = LBound(N) To UBound(N)
        Worksheets(N(i)).Activate
        '
        ' rest of code here
        '
    Next i

  3. #3
    Seeker mmoore5553's Avatar
    Join Date
    Mar 2019
    Posts
    13
    Articles
    0
    Excel Version
    office 365 excel
    Quote Originally Posted by NoS View Post
    To answer what you've asked for
    Code:
    N = Array(1, 2, 3)
    For i = LBound(N) To UBound(N)
        Worksheets(N(i)).Activate
        '
        ' rest of code here
        '
    Next i

    Wow that worked out perfect for what i needed to get done. Thank you so much NOS.

Tags for this Thread

Posting Permissions

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