+ Reply to Thread
Results 1 to 3 of 3

VBA creates named ranges, but named ranges disappear

  1. #1
    Registered User
    Join Date
    10-02-2013
    Location
    St. Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    28

    VBA creates named ranges, but named ranges disappear

    Hi All

    You've all been exceptionally helpful in the past and I hope to whatever gods there are that you can help me again....

    I've created a spreadsheet for our Human Resources department to track how long it takes a new employment position to be filled, from start to finish.

    I use data validation in the Tracking table for ease of use for the enduser, and I use named ranges to reference in the data validation.

    Iím creating a macro that allows the user (at the click of a button) to create new tabs for the new year.

    Almost everything works, but when I specify the named range to use for data validation (both in Excel and in VBA), the named ranges do not exist. To make it even more confusing (to me), after naming the ranges or running the macro (thus creating 2 new tabs), the named ranges exist only when selecting the last tab (named ďTableĒ & year).

    Because the named ranges donít exist (apparently), I cannot use data validation with the new named ranges.

    Why do the named ranges only show up when the tab in which they reside is selected?

    I've included the code below and attached a sample worksheet.

    Please Login or Register  to view this content.
    Thanks in advance for the help!
    BrotherNeptune
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,965

    Re: VBA creates named ranges, but named ranges disappear

    When setting the validation, you need a named range scoped for the workbook rather than just the worksheet.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Registered User
    Join Date
    10-02-2013
    Location
    St. Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: VBA creates named ranges, but named ranges disappear

    Thanks for the quick reply!

    I've managed to change the scope from Worksheet to Workbook, but I'm running into further errors.... I'll play around a bit and see if I can find the solution.

    I may be back but for now.... Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Define only 2 named ranges from a list of named ranges...
    By abhi900 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-29-2014, 04:20 AM
  2. Looping Mutliple Named Resized Ranges in next empty row below another named range
    By gingumdog in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2014, 08:15 PM
  3. [SOLVED] Determining if the value of a cell can be a named range, then assigning named ranges after
    By Romulo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2013, 06:05 PM
  4. Replies: 0
    Last Post: 10-15-2012, 07:24 AM
  5. Copy data in named ranges to a newer version of the same template to identical ranges
    By handstand in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2006, 10:51 AM
  6. Replies: 1
    Last Post: 03-21-2006, 06:40 PM
  7. [SOLVED] Like 123, allow named ranges, and print named ranges
    By WP in forum Excel General
    Replies: 1
    Last Post: 04-08-2005, 02:06 PM

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