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;

ActiveCell.FormulaR1C1 = _
"=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!

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

"=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

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.