How to display Sheet2 data in Sheet1 using VBA

Using VBA and a macros button, I can capture data found on Sheet2 and display it on Sheet1 by using the function below. The problem is if the spreadsheet is renamed to say Financial Statements 2, then the macros won't work..

total = Workbooks("Financial Statements").Sheets("Sheet2").Range("S5").Value

Is there a way to reference the workbook to itself, like using a this statement instead of the spreadsheet name? That way it won't matter what the spreadsheet is named.

Thanks in advance.

What you said is a little confusing.

The problem is if the spreadsheet is renamed to say Financial Statements 2, then the macros won't work..

Do you mean if the workbook is renamed to Financial Statements 2? Then as long as the workbook is the same you can just use:

Sheets("Sheet2").Range("S5").Value

You can always use ActiveWorkbook too which would look like

ActiveWorkbook.Sheets("Sheet2").Range("S5").Value

If you are changing your sheetname then run the macro from whatever sheet you are concerened with and use:

ActiveWorksheet.Range("S5").Value

Does this make sense?

ActiveWorkbook.Sheets("Sheet2").Range("S5").Value


That was exactly what I needed. It worked, thanks.