+ Reply to Thread
Results 1 to 6 of 6

Excel VBA: How to reference sheet names that are based on dates

  1. #1
    Registered User
    Join Date
    08-20-2019
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    6

    Lightbulb Excel VBA: How to reference sheet names that are based on dates

    Hi,

    I currently have a workbook that can generate a new sheet via a macro button that is titled with the current date that the macro button was clicked. E.g "08-15-2019". I have it so that it will take you to the sheet named by the current date if it already exists and if it doesn't, it'll create one (named by the current date). I also have a user form that can be used to print information to the cells of the various sheets.

    I want to be able to reference the sheets to print information on based on the current date that my excel user form is used on. However I do not wish to right 1000s of lines of code just to explicitly reference every possible date for the foreseeable future: e.g Range("08-15-2019!B4").Cells(1, 1) etc.

    I tried to use: Range("Date$!B4").... but given that its within "" I knew it wouldn't work. Furthermore I tried Range(Date$!B4).... but that also doesn't work. How could I get my code to search through the workbook for the various sheets until it finds the one named with current date without explicitly referencing it? Or better yet how can I reference the sheet simply by having the program search for the one named by the current date?

    Many Thanks!
    Last edited by MiguelSPerera; 08-20-2019 at 08:21 AM.

  2. #2
    Valued Forum Contributor nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,718

    Re: Excel VBA: How to reference sheet names that are based on dates

    Is This what you mean
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-20-2019
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel VBA: How to reference sheet names that are based on dates

    So with the user form I have it so that values are entered into various text boxes and then a confirm button is clicked. Within the code for that confirm button I want the various values entered into the text boxes to be printed onto a sheet that corresponds with the current date. I only know how to reference explicitly sheets:

    for example lets pretend one of the text boxes on the user form is named txtOne and the value entered in by the user was "hello" upon clicking confirm:

    Range("Sheet1!A1").Cells(1, 1) = txtOne

    I expect based on the line of code above for "hello" to be printed in cell A1 of the sheet titled "Sheet 1". This all works for me but however I don't want to explicitly reference every single possible date for sheet names.

    Range("15-08-2019!A1").Cells(1, 1) = txtOne
    Range("16-08-2019!A1").Cells(1, 1) = txtOne
    Range("17-08-2019!A1").Cells(1, 1) = txtOne .....etc

    Is there a way, to within that line reference it so that the code searches for the sheet that has the same name as the current date: i.e if the date is the 20th August it'll print the value from txtOne into cell A1 of the sheet named "20-08-2019"

    Thanks

  4. #4
    Valued Forum Contributor nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,718

    Re: Excel VBA: How to reference sheet names that are based on dates

    For the current date sheet

    Please Login or Register  to view this content.
    adapt into your userform code
    Attached Files Attached Files
    Last edited by nigelog; 08-20-2019 at 07:32 AM.

  5. #5
    Registered User
    Join Date
    08-20-2019
    Location
    Derby, England
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel VBA: How to reference sheet names that are based on dates

    Thank you very much!! I'll start integrating it into the user form code.

  6. #6
    Valued Forum Contributor nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,718

    Re: Excel VBA: How to reference sheet names that are based on dates

    Have a look at this way of addressing your ranges.

    Workbook attached

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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