Thread: Automatic naming Worksheet tabs

Hi All,

Here is a curly one....I have the automatic worksheets being renamed by a vba code. The problem I'm having is there is a macro button to hide and unhide these same sheets. Once the names have been changed, the sheets won't open for THAT exact reason, the macro can no longer find the sheet names.

Is there any way around this so I can have my cake AND eat it too????

Thanx
Sandi

Worksheets have 2 names; WS.Name that appears on the sheet tab in Excel and
WS.CodeName that is only accessible from VBA.
So you can use the .CodeName to refer to sheets whilst changing their .Name,
which then becomes irrelevant as far as your code is concerned.

NickHK

"rhani111" <rhani111.2a04q3_1151312498.7007@excelforum-nospam.com> wrote in
message news:rhani111.2a04q3_1151312498.7007@excelforum-nospam.com...
>
> Hi All,
>
> Here is a curly one....I have the automatic worksheets being renamed by
> a vba code. The problem I'm having is there is a macro button to hide
> and unhide these same sheets. Once the names have been changed, the
> sheets won't open for THAT exact reason, the macro can no longer find
> the sheet names.
>
> Is there any way around this so I can have my cake AND eat it too????
>
> Thanx
> Sandi
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=555489
>

Thanx so very much, but what code do i use in both the open sheets macro and the close macro?

Yes, uses the codename to hide and unhide the sheets, not worksheet name.
The codename can be found in the VBIDE where the worksheet object is shown
as Sheet1(Sheet1), the codename is the name before the brackets).

You would use like

codename.Visible = xlSheetHidden

rather than

Worksheets("sheetname").Visible = xlSheetHidden

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"rhani111" <rhani111.2a04q3_1151312498.7007@excelforum-nospam.com> wrote in
message news:rhani111.2a04q3_1151312498.7007@excelforum-nospam.com...
>
> Hi All,
>
> Here is a curly one....I have the automatic worksheets being renamed by
> a vba code. The problem I'm having is there is a macro button to hide
> and unhide these same sheets. Once the names have been changed, the
> sheets won't open for THAT exact reason, the macro can no longer find
> the sheet names.
>
> Is there any way around this so I can have my cake AND eat it too????
>
> Thanx
> Sandi
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=555489
>

hmm.....I am still not sure...(not blonde but am not good with VBA...lol)...here is an example of the close button...

Range("B1").Select
Sheets(Array("Shot 1", "Shot 2", "Shot 3", "Shot 4", "Shot 5", "Shot 6", "Shot 7", _
"Shot 8", "Shot 9", "Shot 10", "Shot 11", "Shot 12", "Shot 13", "Shot 14", "Shot 15", _
"Shot 16", "Shot 17", "Shot 18", "Shot 19", "Shot 20", "Shot 21", "Shot 22", "Shot 23" _
, "Shot 24", "Shot 25")).Select
Sheets("Shot 1").Activate
Sheets("Shot 26").Select Replace:=False
Sheets(Array("Shot 1", "Shot 2", "Shot 3", "Shot 4", "Shot 5", "Shot 6", "Shot 7", _
"Shot 8", "Shot 9", "Shot 10", "Shot 11", "Shot 12", "Shot 13", "Shot 14", "Shot 15", _
"Shot 16", "Shot 17", "Shot 18", "Shot 19", "Shot 20", "Shot 21", "Shot 22", "Shot 23" _
, "Shot 24", "Shot 25")).Select
Sheets("Shot 1").Activate
Sheets(Array("Shot 26", "Shot 27")).Select Replace:=False
Sheets(Array("Shot 1", "Shot 2", "Shot 3", "Shot 4", "Shot 5", "Shot 6", "Shot 7", _
"Shot 8", "Shot 9", "Shot 10", "Shot 11", "Shot 12", "Shot 13", "Shot 14", "Shot 15", _
"Shot 16", "Shot 17", "Shot 18", "Shot 19", "Shot 20", "Shot 21", "Shot 22", "Shot 23" _
, "Shot 24", "Shot 25")).Select
Sheets("Shot 1").Activate
Sheets(Array("Shot 26", "Shot 27", "Shot 28")).Select Replace:=False
Sheets(Array("Shot 1", "Shot 2", "Shot 3", "Shot 4", "Shot 5", "Shot 6", "Shot 7", _
"Shot 8", "Shot 9", "Shot 10", "Shot 11", "Shot 12", "Shot 13", "Shot 14", "Shot 15", _
"Shot 16", "Shot 17", "Shot 18", "Shot 19", "Shot 20", "Shot 21", "Shot 22", "Shot 23" _
, "Shot 24", "Shot 25")).Select
Sheets("Shot 1").Activate
Sheets(Array("Shot 26", "Shot 27", "Shot 28", "Shot 29")).Select Replace:=False
Sheets(Array("Shot 1", "Shot 2", "Shot 3", "Shot 4", "Shot 5", "Shot 6", "Shot 7", _
"Shot 8", "Shot 9", "Shot 10", "Shot 11", "Shot 12", "Shot 13", "Shot 14", "Shot 15", _
"Shot 16", "Shot 17", "Shot 18", "Shot 19", "Shot 20", "Shot 21", "Shot 22", "Shot 23" _
, "Shot 24", "Shot 25")).Select
Sheets("Shot 1").Activate
Sheets(Array("Shot 26", "Shot 27", "Shot 28", "Shot 29", "Shot 30")).Select _
Replace:=False
ActiveWindow.SelectedSheets.Visible = False
Range("C8").Select

__________________________________________

What do i change here to do what you have explained so far...

Thanks again
Sandi