How can I activate the sheet that is running code from a VBA module?

Hi

This will hopefully be fairly straight forward.

I would like to reference the current worksheet in vba code so that I can activate the sheet that is running the code. I need to do this as I am switching between 2 wordbooks and the workbook name in 1 instance is not fixed.

I tried retrieving the workbook name like this:

Please Login or Register  to view this content.

but could not work out how to implement that in code such as:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The above does not work and I tried a few variations

I did not know how to deal with the quotation marks and parenthesis.


I have another option, the workbook name is contained in a cell on a worksheet within the workbook, the name is in text form minus the .xlsm part so this might be of use.



Can anybody possibly help me achieve the desired outcome?



Many thanks in advance, your wisdom is always appreciated.

Regards,

Michael.

Hi Mick86,

Please Login or Register  to view this content.

Hello and thank you for the speedy reply.

So with the above code do I can place that as a function then call in from another sub using

Please Login or Register  to view this content.

???

I tried this but it didn't seem to work. I'm not sure if I can include the Function as part of the Sub. I assume if this function runs before I switch worksheets it retains memory of the earlier active worksheet as I need to call the function while a different sheet is active.

My current coding set up is as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I use 1 module to check if a certain workbook is open or not then call a macro to make some changes, then I need to return to the original worksheet which could have various names and as such I need to reference it in a cleaver fashion. My current index reference only works if I open the files in the right order.

Something Like this:

Please Login or Register  to view this content.

Without the function:

Please Login or Register  to view this content.

Because the function gives you the Active Book Name - it's already activated!

Hi mick86,

To get a reference to the original workbook:

Please Login or Register  to view this content.

right at the start!

You're welcome!