+ Reply to Thread
Results 1 to 5 of 5

Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    202

    Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

    Let's suppose I have WorkbookA, which contains worksheets with codenames FirstASheet and SecondASheet. It also includes a named range MyRangeA (scoped to Workbook), and two ranges named SheetRangeA (scoped to FirstASheet and SecondASheet, respectively).
    And let's suppose I also have WorkbookB, which contains worksheets with codenames BeginBSheet and EndBSheet. It also includes a named range MyRangeB (scoped to Workbook), and two ranges named SheetRangeB (scoped to BeginBSheet and EndBSheet, respectively).
    Note that the sheet names I'm using are the CODEnames, not the names on the sheet tabs.
    Finally, suppose I have a macro in WorkbookA, that opens WorkbookB and manipulates data between the two files.

    Can someone please explain to me how to reference the various sheets and named ranges within that macro? I'm used to simply using:
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    when working with a single file. But with two files open, it seems like Excel doesn't recognize references to the sheets or ranges in WorkbookA when WorkbookB is activated (and vice versa). Even if the names are unique.
    What are the rules as far as which names are recognized for the active workbook and for ThisWorkbook (the file running the macro)? How do I refer to a sheet codename (or named range) in the non-active workbook?

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

    You have hit a limitation of sheet codenames
    - they can only be used to refer to sheets in the workbook in which the code resides
    - and to refer to a sheet level name you need to be able to refer to the sheet
    - so it is easier using Sheets(1) or Sheets("SheetName") notation

    There are various ways to use the codenames in a diffrent workbook but it adds complication. Example
    Please Login or Register  to view this content.
    Another option would be to place MacroB in WorkbookB using sheet codenames in WorkbookB
    - MacroB could be then called by the macro in WorkbookA
    Last edited by kev_; 01-26-2019 at 07:16 AM. Reason: added another option
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,151

    Re: Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

    Quote Originally Posted by Merf View Post
    ... with two files open, it seems like Excel doesn't recognize references to the sheets or ranges in WorkbookA when WorkbookB is activated (and vice versa ...
    A little "replacement" for this case - copy to one directory and run macro from "WorkbookA.xlsm"
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 365
    Posts
    202

    Re: Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

    OK, thanks for the info! I already loop through the all worksheets in WorkbookB (by codename) when the macro opens it (to confirm that the correct file has been opened and contains the required sheets), so I can just assign a worksheet variable to each sheet when I find it, and then use that sheet variable from then on.

    So, do codename references to sheets in ThisWorkbook still work OK, even when WorkbookB is the active workbook?
    And when does the named range short notation ([MyRangeA], for example) work (or not work)? If specified alone (with no workbook specified) is it presumed to apply to ThisWorkbook, or the active workbook? And can you use:
    Please Login or Register  to view this content.
    and so on, or must you (when specifying the sheet or workbook) use the "long form":
    Please Login or Register  to view this content.
    And will that last one even work (even though MyRangeB has a workbook scope), since Range is a Worksheet property, not a Workbook property?

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Referencing Named Ranges and Sheet Codenames in Multiple Workbooks

    So, do codename references to sheets in ThisWorkbook still work OK, even when WorkbookB is the active workbook?
    Test with
    Please Login or Register  to view this content.

+ 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. Sheet codenames work in array but not named range
    By StevePM in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 04-12-2016, 08:43 AM
  2. [SOLVED] Referencing Named Ranges
    By par0016 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2013, 03:34 PM
  3. Referencing Named Cell Ranges
    By K_REY_C in forum Excel General
    Replies: 6
    Last Post: 03-01-2007, 01:42 AM
  4. Referencing named ranges within an Add-In
    By Tristan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-14-2006, 10:31 AM
  5. Q about referencing named ranges
    By 42N83W in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2006, 04:45 AM
  6. Referencing Named Ranges
    By ExcelRookie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2006, 08:55 AM
  7. Referencing named ranges
    By FinChase in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-04-2005, 11:05 AM

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