How do I make ThisWorkbook.Name a global variable?

Please Login or Register  to view this content.

I am trying to make a global variable that takes the value of ThisWorkbook.Name when opened. This way I can change the excel files name but use a variable in my code when switching between 2 workbooks. Can someone show me the syntax to do this? In the example code I declare a public variable and then attempt to rename it when opening the workbook. However, in WorkSheetExists the "subscript is out of range."

Hi,

In your code there's no need for the "", it won't recognise it as a variable with it:

Please Login or Register  to view this content.

You could have the workbook as an option argument in the function:

Please Login or Register  to view this content.

You can then call it like this specifying the workbook or just leave it out to operate on the active workbook:

Please Login or Register  to view this content.

Dom

workbooks(2) won't change by renaming a workbook.

There's no need to 'activate' or 'select' in VBA.

you can add a sheet using:

Please Login or Register  to view this content.

workbooks(1): personal.xls
workbooks(2): secondly opened workbook

to verify:

Please Login or Register  to view this content.