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

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" wrote in
message ...

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

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" wrote in
message ...

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?


--
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


--
rhani111
------------------------------------------------------------------------
rhani111's Profile: http://www.excelforum.com/member.php...o&userid=19940
View this thread: http://www.excelforum.com/showthread...hreadid=555489