+ Reply to Thread
Results 1 to 6 of 6

Macro with variable reference to the worksheet tab

  1. 02-23-2014, 05:53 PM #1
    tuph
    tuph is offline
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Macro with variable reference to the worksheet tab

    Every morning I run a report and create a new worksheet in this month's workbook - sample attached.

    I then manually create the following VLOOKUPs to populate columns H, I and J:
    Please Login or Register  to view this content.
    My macro works OK but has absolute values for the TAB name which I have to edit every day before running it. I would like automate this to reference yesterday's tab. "Yesterday" needs to be the date immediately preceeding the current tab taking into account that some dates will be missing because of weekends.

    I would appreciate any suggestions!
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved

    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments
    Reply With QuoteRegister To Reply
  2. 02-23-2014, 05:56 PM #2
    tuph
    tuph is offline
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Macro with variable reference to the worksheet tab

    Oops - forgot the attachment!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-16-2014, 08:37 PM #3
    tuph
    tuph is offline
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Macro with variable reference to the worksheet tab

    bump - is anyone able to help me with this problem please?
    Reply With QuoteRegister To Reply
  4. 03-16-2014, 09:02 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Macro with variable reference to the worksheet tab

    Since this deals with VBA you'll have a better chance of getting a solution if you post it in the Excel Programming / VBA / Macros forum.

    Contact a moderator/administrator and ask them to move this thread into that forum.

    You can click the Report Post button and include a message that you would like to have the thread moved. The report will be seen by the mods/admins. No telling how long it will take but it will eventually get moved.

    Good luck!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 03-16-2014, 09:21 PM #5
    cffndncr
    cffndncr is offline
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    Re: Macro with variable reference to the worksheet tab

    Unfortunately the way you have named your tabs makes this difficult. It's possible to get a reference to the date of the last working day (using WORKDAY(TODAY(),-1), but this returns an excel date reference (ie. 41712 instead of 14.03.14).

    The only other possibility I can think of is if you have a cell somewhere on the worksheet where you type in the date of the last workday (ie. You type in 14.03.14). For example, if you had cell A1 as your 'last workday' cell, you could use that in your first VLOOKUP formula like this.

    =VLOOKUP($A2,INDIRECT("'"&$A$1&"'!$A$1:$J$30",8,FALSE)
    Reply With QuoteRegister To Reply
  6. 03-16-2014, 10:23 PM #6
    tuph
    tuph is offline
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Macro with variable reference to the worksheet tab

    Thanks Tony. I'll give it a try.

    Thanks for your suggestion cffndncr.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how do i reference a worksheet variable in a formula?
    By chemeng1T3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2012, 08:27 AM
  2. Is it possible to reference a variable worksheet name in a formula?
    By HelpExcel in forum Excel General
    Replies: 2
    Last Post: 10-06-2009, 04:32 AM
  3. Vlookup with variable worksheet reference
    By trempnvt in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-23-2006, 01:50 PM
  4. how to reference a worksheet with a variable vs a name
    By ibbm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2006, 03:00 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1