+ Reply to Thread
Results 1 to 7 of 7

workbook open then show week tab according today date

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    workbook open then show week tab according today date

    I have workbook that have 52 sheet in, sheets are arrange by weeks like (01jan, 08 jan, 15 jan). Weeks start from sunday to saturday. so i want when workbook open it show the tab around today's date. Exmple If today is jan 10 then it show the tab 08 jan or 15 jan, Not sure if there is way to show only tab Jan 08 beacuse jan 10 date fall in that period. But if it show around that tab work for me. I do not want see last month of the year where other user saveed it last time.

  2. #2
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: workbook open then show week tab according today date

    any other way , or if change my tab name ?

  3. #3
    Registered User
    Join Date
    07-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: workbook open then show week tab according today date

    Hi Robrobet,

    You could try adding an Auto_Open macro like this:

    Please Login or Register  to view this content.
    This code assumes that the days will always have two characters in the sheet tab name, and that the month part will always be three characters. If this isn't the case then you will need to amend the code accordingly. Also, you could refine the (Date-7) and (Date + 7) parts to refine so that only one week tab appeared.

    The other issue with hiding sheets is that at least one sheet always needs to be visible. If the current week tab wasn't currently visible, and this routine tried to hide all of the other tabs before un-hiding the current week tab then Excel would cause an error to occur (this is why I added the On Error Resume Next line). An alternative is to first unhide all of the worksheets in the code and then run my code.

    Hope this helps,
    Simon.

  4. #4
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: workbook open then show week tab according today date

    Hi Simmon,

    Thanks for your reply and your time,
    just want to clear up little more that I do not want to hide any sheet from workbook. I want all the sheet to show up in workbook but when i open it open the tab that close to today date, Because other user when close it they some time work on dec month and save it on that tab. I know it is easy to scrool back to other tab but just looking up vba that open up tab near to today date

    Thanks
    RR

  5. #5
    Registered User
    Join Date
    07-19-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: workbook open then show week tab according today date

    Hi RR,

    That's easy enough to do - just change the sh.visible=xlSheetVisible code to sh.activate, and then delete the next two lines of code (the ELSE, the next sh.Visible to just leave the END IF line. You can only have one sheet active (obviously) so you may need to refine the date test so that it selects the right sheet for you (ie either the greater than or less than criteria).

    Kind regards,
    Simon.

  6. #6
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: workbook open then show week tab according today date

    I try like this but it did not work and also it did not show any error as well

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    165

    Re: workbook open then show week tab according today date

    Hi Simon,

    Sorry it was my fault that code did not work, My tab names are Jan 01, jan08 but I mention as 01 jan , 08 jan. So code work it but it show me the tab Aug 29 which is 4 week away from todays date.
    Any idea why is that ?
    thanks in advance for your time and help
    RR

+ 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] 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
  2. update date of the week/month compared to today
    By tbar05 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2012, 05:24 AM
  3. [SOLVED] Show today's date on calendar
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2012, 04:23 PM
  4. Show today's date if specified cell is modified
    By xavpg in forum Excel General
    Replies: 7
    Last Post: 02-28-2012, 07:16 PM
  5. Replies: 2
    Last Post: 02-02-2011, 06:47 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