Changing Sheets Codename (Excel 97) 1

[AirZebra]

Hi all,

Does anyone know if it is possible to change a worksheet's codename through VBA in Excel 97 (to prevent copied sheets being named Sheet12, Sheet 121, Sheet 1211, Sheet 12111 etc.)?

I am using the Pivot Tables Show Pages option to create new sheets with each specific page item on a new sheet.

Regards,

Alan

 

[xlbo]

see this FAQ:
faq707-4090

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[AirZebra]

Hi Geoff,

I have tried to use the code from the FAQ but if I use an aplha-numeric entry it brings up an error on the line:

If NewRef = vbCancel Or NewRef = "" Then [\code]

The error is "Run-Time Error 13:  Type Mismatch".

If I use a numeric entry it says the entry already exists.

Do you have any further ideas?

Regards,

Alan

 

[xlbo]

did you copy it exactly as is ???
If you did, you'll notice that newRef is not dimmed as any type. This would be intentional so it will default to Variant which can take both strings and values. This being the case, it should work......Mike (Bowers74) - you about to comment on this ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[AirZebra]

Sorry,

I really should try harder before I post in here.

The error message was associated with the IF statement not the line that changed the codename. Because I am going to be using data from a pivot table for the codename, the code supplied in the FAQ can be modified to work exactly as I would like it.

Thanks again Geoff.

Regards,

Alan