+ Reply to Thread
Results 1 to 8 of 8

Open sheet with date =today()

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Open sheet with date =today()

    I have a workbpook with several hundred sheets including a sheet for each day of the year. The sheets named after the days are hidden. I want to open up to the sheet that matches =today() but I'm battling. I've attached a sample workbook and grateful for all help.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: Open sheet with date =today()

    Hi,

    Yes you could use a button to fire a macro but better still use the Workbook Open Event so that the relevant sheet is opened automatically.
    You haven't told us the precise structure of the sheet tab text so I'll assume it's ddmmyyyy, e.g 29122019

    The code would be

    Please Login or Register  to view this content.
    That said, why are you keeping each days data on its own sheet resulting in hundreds of sheets.
    For any serious application where you need to analyse data you should keep all data on a single sheet with an extra column to contain the record dates.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Re: Open sheet with date =today()

    Hi Richard. Thanks for that. Following up on your comment:

    1. Say, I have a daily data entry sheet.
    2. Say, 12 values are entered daily.
    3. I have a data 'summary' sheet which is to be used to record and retain all 365 day's 12 values

    How do I get the 12 values from the daily data entry sheet into the summary sheet? Is that just a copy paste approach triggered on exiting the workbook?

  4. #4
    Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    199

    Re: Open sheet with date =today()

    Hello,

    As you want to make a date visible you need to make the others invisible so here

    Please Login or Register  to view this content.
    All this code goes in the ThisWorkbook Code sheet

    Grtz
    Last edited by Joske920; 12-29-2019 at 07:24 PM.
    Please be as complete as possible in your asking so it may save use all the time to rework the solution because you didn't give all the requirements. If you have a layout in mind please work it out first so we can adapt our solution to it. Thanks.
    If you have been helped, maybe you could click the *

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: Open sheet with date =today()

    Quote Originally Posted by BRISBANEBOB View Post
    Hi Richard. Thanks for that. Following up on your comment:

    1. Say, I have a daily data entry sheet.
    2. Say, 12 values are entered daily.
    3. I have a data 'summary' sheet which is to be used to record and retain all 365 day's 12 values

    How do I get the 12 values from the daily data entry sheet into the summary sheet? Is that just a copy paste approach triggered on exiting the workbook?
    The best approach would be a macro that does the copying and pasting for you automatically.

    We need to understand the layout of your sheets for more specific help. If you upload the workbook with say half a dozen sheets example workbook which accurately reflect the data then no doubt we can help further

  6. #6
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Re: Open sheet with date =today()

    I have attached a reworked sample workbook. The code I am trying to use to find the next blank cell in the summary page doesn't work. Secondly, what if the user saves twice on the same day? That would append the data again.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-30-2018
    Location
    Vlaams Brabant Belgium
    MS-Off Ver
    365
    Posts
    199

    Re: Open sheet with date =today()

    Hi,

    This part will copy all entry and add to the data without the empty rows

    Please Login or Register  to view this content.
    To check if you already did this, maybe you could use a macro to look for all data from the date and remove it?
    Grtz

  8. #8
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    792

    Re: Open sheet with date =today()

    T%hank you Grtz, that works perfectly. I have developed a formula solution to data already entered. Your help is much appreciated

+ 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. Macro: check today date with start date and open a file in stored location
    By Topa2 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-10-2016, 11:39 AM
  2. [SOLVED] On file open, automatically scroll to today's date
    By Tony Valko in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-28-2013, 04:13 PM
  3. workbook open then show week tab according today date
    By robrobet in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-04-2013, 04:20 PM
  4. [SOLVED] When workbook open it show the sheet have today date
    By robrobet in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-24-2013, 11:00 AM
  5. How to get macro to open file name based on today's date?
    By ytc1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-05-2013, 05:37 AM
  6. [SOLVED] save currently open file appended with today's date
    By roothog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-05-2011, 10:41 PM
  7. Open an Excel file with a date as today in a macro
    By Perry in forum Excel General
    Replies: 4
    Last Post: 11-10-2005, 06:00 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