Dealing with sheets codenames

[sprites]

Hello

I am trying to write some code in VB that will be able to function right in some different sheets, that I dont know their name in advance (the user can duplicate sheets using a macro). First, after a long time of googling I still havent found a decent way to retrieve the active sheet Codename from which I called the function (from a button).

Next, even if I knew the Codename of the current sheet, how do I use it inside the code, for example, replacing:

Worksheets("somename").Range("A1").Value
to
[variable].Range("A1").Value ?

Is it possible?

thanks

[Mumps1]

Hello sprites. It's not clear what you are looking for. Could you post a copy of your file with a detailed explanation of what you want to do? It would be easier to help if we could see what you're working with.

[TMS]

In the immediate window, type: ?Activesheet.Codename

Unless you have changed it, the codename is, for example, Sheet1 for Sheets("Sheet1"). Again, if you haven't changed anything, Sheets(4) is the same thing as Sheets("Sheet4") and the same as just Sheet4 ... assuming you have 4 sheets in your workbook.

So, instead of referring to Sheets("Sheet4") you could refer to Sheet4, for example, Sheet4.Range("A1") or even Sheet4.[A1]

Hope this helps clarify how you find it and how you use it.

You can only change it in the VBE or in code but, I believe, changing it in VBA is possible but not recommended.

You'd usually change it when you want to know which sheet you are using regardless of what others may have changed the (visible) sheet name to.


Regards, TMS

[sprites]

Hi,

I cannot upload the original file to the net, so heres a quick and simplified example of what I'm trying to do:

UsingCodeName.xlsm

Assuming I don't know the name/codename of the sheet in advance to write it inside the code, how can I manipulate the current sheet codename to do all sort of actions in the sheet? (pulling a number from a known sheet in the example here)

Thanks for the help

[Mumps1]

You can refer to the current sheet that is visible as the "ActiveSheet" regardless of its name or Codename. If you want to run a macro on the visible sheet, just refer to it as the 'ActiveSheet'. If you want to run the same macro on all the sheets, you can use a loop similar to this:

Please Login or Register  to view this content.

When you loop through all the sheets, you don't need to know their names because they are accessed using the variable 'ws'. I hope this helps.

[sprites]

The problam is that I do not necessarily have the sheet visible or active at all times, and some of the functions constantly run in the background with application.Volatile.
I can also have three or four of these sheets simultanously so I can't risk it using activesheet (except for once to declare this codename perhaps)..

[TMS]

See: http://www.cpearson.com/excel/codemods.htm

Note my previous post. It is the norm to set the sheet's codename in the VBE before you use the name. This allows you to access the internal name regardless of what the user has done up front.

In terms of referring to a copied sheet, it is common practice to copy a sheet and, while the new sheet is still the active sheet, set a variable equal to the new sheet. For example:

Please Login or Register  to view this content.

Note that when you copy a sheet, it will be allocated the next available internal number by Excel


Regards, TMS

[sprites]

I ended up using some of you tips and just using:

shtname = ActiveSheet.Name

and then just refering to

Sheets(shtname)

whenever I needed and it works just fine, thanks for the help!

[TMS]

You're welcome. Thanks for the rep.

You can also use:

Please Login or Register  to view this content.

And then refer to ws.


Regards, TMS