Sheet selection/naming difficulty

[melvin]

Hi,

I have recorded a macro with a small, but annoying, problem. It is
programmed to 'Grand Total' a Pivot Table report, which in turn
automatically creates a new sheet. Each time a new sheet is created
it's sheet number increases by one (i.e. 1st total creates sheet 1, 2nd
total = sheet 2, 3rd total = sheet 3... and so on). The macro is
programmed to 'Total' several times, and so creates a series of sheet
numbers. I would like to have control over the naming of these sheets
because the Book I am working in contains links to these sheets under
specific names.

Is there any way the sheet names/numbers can be controlled so that
Visual Basic can be programmed to rename them in the macro??

I have attached a snapshot of the vB code if it will help to take a
look!

Many thanks for any assistance you are able to provide.

Cheers folks,

Melv


+----------------------------------------------------------------+
| Attachment filename: sheetselection.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=370960|
+----------------------------------------------------------------+

 

[mseyf]

When you add a sheet, capture that sheet's name to a variable, then you
can later use that variable to rename the sheet.

Dim ShtName As String

ShtName = ActiveSheet.Name
...
...
Sheets(ShtName).Name = "SB - C(1)"

you would have to set up multiple variables if you're adding multiple
sheets.