Looking for a way to have the user select which sheets the code will run on
Results 1 to 2 of 2

Thread: Looking for a way to have the user select which sheets the code will run on
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looking for a way to have the user select which sheets the code will run on

    I currently have vba code that runs through all of the named ranges in the workbook and for every sheet within an array of all the sheets in the work book it will find a specific named range and copy that data to different data dump location.

    What I would like, is for the user to be able to select which sheets out of all the sheets in the workbook they want the code to run on specifically, instead of it running based on a defined/static array of sheets

    Here is the part of the code that I would like to change:

    Code:
    Dim WshtNames As VariantDim WshtNameCrnt As Variant
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim UpldNames As Variant
    Dim UpldNameCrnt As Name
    
    
    ''''Determine applicable tabs. 
    WshtNames = list of tabs with yellow boxes for upload purposes
    WshtNames = Array("NewShare", "1590_Migration Depts", "PrjSvc", "Talent Mgmt CoE", "COS Intra-COS Alloc", "ADP RM", "Benefits CoE", "TLMeTimeUsage", "BenefitsToCOS", "Division", "TLM_CoE", "TLM_Depreciation", "FulfAllocSHPS", "FulfAllocNonSHPSBen", "ProB_COS", "PSC_COS", "COS_SASS")
    
    
    
    
    For Each WshtNameCrnt In WshtNames
       With Worksheets(WshtNameCrnt)
       Worksheets(WshtNameCrnt).Activate
        
          For Each UpldNameCrnt In ActiveWorkbook.Names
             
             If Left(UpldNameCrnt.Name, 6) = "Upload" Then
                
                  If UpldNameCrnt.RefersToRange.Parent.Name = ActiveSheet.Name Then
                
    [THIS SECTION IS WHERE THE MAIN COPY/PASTE CODE GOES I LEFT IT OUT TO SAVE SPACE]             
                  End If
              End If
           
           Next UpldNameCrnt
    Thank you all in advanced for the assistance.
    0 0
     

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,469
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Looking for a way to have the user select which sheets the code will run on

    Duplicate to https://www.mrexcel.com/forum/excel-...run-macro.html

    Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

    Note that posts from new members are sometimes 'held back' until they are approved by a moderator. That had happened with the above post and you would have been presented with a message to that effect. So in future, please be patient.

    Any bumps, clarifications, or follow-ups should be posted to the linked thread.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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