+ Reply to Thread
Results 1 to 4 of 4

Referencing a sheet in the active workbook

  1. #1
    Forum Contributor
    Join Date
    10-12-2018
    Location
    Leesburg, FL
    MS-Off Ver
    2013
    Posts
    113

    Referencing a sheet in the active workbook

    I have a program that needs to read a specific tab of the current open workbook. The workbook's name will vary though so I need it to reference the active one. The program works if the sheet I need to get the data from is active. However,
    I'd like it to read that data even if it isn't the active sheet.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Referencing a sheet in the active workbook

    It is not clear to me what is currently happening... but perhaps "Set LookupRange = Range("A2:B21")" is the issue... since that ONE line is going to make your LookupRange the active sheet... Also, there is a bit of redundant code... you are setting a worksheet object and then complicating the code a little by using its name, instead of just using the object.. Try:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 01-14-2019 at 12:04 PM. Reason: updated the do while statement to also use the objWorkSheet object instead of re-stating the same worksheet name.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    10-12-2018
    Location
    Leesburg, FL
    MS-Off Ver
    2013
    Posts
    113

    Re: Referencing a sheet in the active workbook

    Arkadi-
    That worked! Thank you for the explanation and code changes.

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Referencing a sheet in the active workbook

    My pleasure!

    Just to elaborate, any time we don't specify the workbook or worksheet by name in the statement ( such as that Range(... ) statement, then the active one is implied.

    Appreciate the feedback, rep and the marking of the thread as solved!

+ 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. [SOLVED] Referencing active workbook file name in code automatically.
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-24-2014, 10:18 AM
  2. [SOLVED] save active sheet in new workbook, naming it as cell value of active sheet
    By arkharova.s in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-18-2014, 06:16 AM
  3. [SOLVED] Trouble copying data from closed workbook into active workbook, referencing help
    By lepperga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2013, 01:48 PM
  4. Referencing value from Active cell of another sheet
    By hiteshdemla in forum Excel General
    Replies: 8
    Last Post: 02-24-2013, 03:27 PM
  5. Referencing Active Cell's Value from Another Sheet
    By BusTeacher in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-24-2013, 09:15 AM
  6. Macro to Print active sheet as PDF to Active workbook and customize name
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 08:52 PM
  7. how can worksheet_activate event of active sheet work of active workbook
    By tahir mahmood in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2011, 08:19 AM

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