Posts by russ_stein

    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! :)

    Hi Roy


    Getting the same error - 1004.


    Whatever i have tried i am constantly getting this error, so starting to think there might not be a way to achieve what I'm looking for.


    Alternatively - Is there another way i can create another macro, which will do a find & replace in my module automatically to change the sheet name to today?


    I'm thinking if I created some code, where it changes Dec 3 into Dec 4, etc etc just by hitting a button.

    Hi all,


    I am relatively new to VBA, so far so good however I am stuck on a particular function which is really causing me some grief.


    The macro I created uses a VLOOKUP, which references a worksheet from another workbook. The issue is, the workbook being used creates a new worksheet daily.


    My Current formula (which works) is the following;


    Code
    1. ActiveCell.FormulaR1C1 = _
    2. "=VLOOKUP(RC[133],'[Unavista UTI Lookup December2019.xlsm]Dec 3'!C4:C7,4,0)"


    Not to sure what the RC[133] is about, as my starting cell is "EH2".

    However, where Dec 3 is stated, that is the worksheet that changes daily in the format of "MMM D" (another macro creates this but has no impact on my macro).


    So my question here is

    1 - How can I reference a newly created worksheet without having to manually change the date in the VBA window

    2 - Is it possible to reference a new worksheet or does it have to have the name of the worksheet?

    3 - Ideally i am looking for the easiest way to write the formula


    Thanks, I hope someone can provide the answer as its been bugging me for a week now! :(