How to open a file which changes name each month

    Hello,


    I have built a macro which opens up another file each month, the issue is that specific file changes name each month (dependent on the month itself)


    At the moment the file name is - Unavista UTI Lookup January2021.xlsm which is in Cell P18

    so my formula for opening the file is


    mylookupfile = Range("P18")


    I am not to experienced on date formats, so would it be possible to confirm how this can be automated each month, rather than me having to go into the file and type over the current month in cell P18


    Thanks! :)

    Hello,


    You could test following in cell P18

    Code
    ="Unavista UTI Lookup"&" "&TEXT(DATE(1,MONTH(TODAY()),1),"mmmm")&YEAR(TODAY())&".xlsm"

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello,


    thanks, however i got a run 1004 error .


    I tried converting the formula into the VBA itself rather then having the code on the excel but unfortunately did not work

    Hello,


    Difficult to correct the mistake you have made in the macro you have not posted ...


    Code
    mydate = "Unavista UTI Lookup " & Format(Now(), "mmmm") & Year(Date) & ".xlsm"


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Quote from russ_stein

    perfect, that works great. thank you very much!8)


    Thanks for the feedback :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login