Refering to sheets number instead of name

[mrjed]

Hello everyone,

I'm new to excel in professional environment and thus am seeking for help.

Basically I want to automate some of my work using macros in excel.

I am working with a workbook which gets a new spreadsheet daily or every other day. When adding a new sheet, so far, I manually type formulas such as VLOOKUP in order to get data from an older sheet within the same workbook. So far I am able to write an macro, that uses the name of sheets as reference (just like in formulas). But what I want is that it uses the order number of the sheet instead. This way when running the macro on a new sheet, it would always use the most recent other sheet (the one, one to the right). I know the command Sheets(3).Select. for VBA, but this hasnt worked out for me yet, because I want it to refer inside a formula...

Also, I would prefer not to use the INDIRECT formula...

I am looking forward to your suggestions.

Kind regards.