perfect, that works great. thank you very much!
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
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
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.
Not sure i'm following -
my formula is
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
I'm getting a Syntax error on your one above.
Before the formula starts, I have
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] ?
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
Not to sure what the RC 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!