How to reference a new worksheet using a vlookup in a Macro

  • 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! :(

  • Your line of code uses ActiveCell, does this mean you have to select the cell before running the macro? If not how can you be sure that the cell is correct?


    You could use a cell to enter the date you need to use, e.g. A1 then amend the code


    Code
    1. ActiveCell.FormulaR1C1 = _
    2. "=VLOOKUP(RC[133],'[Unavista UTI Lookup December2019.xlsm]" format(Range("A1").Value,"MMM D") &'!C4:C7,4,0)"
  • Hi Roy,


    I'm getting a Syntax error on your one above.


    Before the formula starts, I have

    Range("E2").Select


    So the ActiveCell will always be E2.


    is this part - format(Range("A1").Value,"MMM D") - Referring to the latest tab created in the file [Unavista UTI Lookup December2019.xlsm] ?

  • Hi Roy,

    Not sure i'm following -


    my formula is

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


    Dec 3' is the tab being referenced, which changes daily, so at the moment its Dec 4'.

    I'm Looking for a way to formulate the lookup so it finds the new tab created this morning

  • Remove the Range("E2").Select, then try this. If you specify the cell you don't need to select it


    Code
    1. Range("E2").FormulaR1C1 = _
    2. "=VLOOKUP(RC[133],'[Unavista UTI Lookup December2019.xlsm]" & Format(Range("A1").Value, "MMM D") & "'!C4:C7,4,0)"
  • 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.