Inserting Sheets and Naming

[Dan Gesshel]

Hello.

I want to insert a sheet and then name it. That part is done, and not the
problem.

Sheets.Add
Sheets("Sheet1").Name = "TransposedVB"

What happens is the code provides a specific name to the newly inserted
sheet. (In this case, Sheet1.)
If the user deletes that sheet for whatever reason, while the workbook is
still open, when you add the next sheet it will be now named Sheet2, thus
breaking the code. The user would have to close the workbook and reopen it
to execute the code again and have it work.

So, bascially what I'm looking for is the proper syntax (x + 1?) for adding
the new sheet each time Excel changes the Sheet name allowing me to rename
it. I know this is relatively easy, but I'm having a brain lapse at the
moment.

Any help would be greatly appreciated.

Thanks.

Dan

 

[Township of East Hanover]

Dim NewSheet As WorkSheet

Set NewSheet = Workbook.Sheets.Add
NewSheet.Name = "TransposedVB"

 

[Rob Bovey]

Hi Dan,

Is something like this what you're looking for?

Worksheets.Add.Name = "TransposedVB"

Or if you need to get a reference to the new sheet to do other things
besides just name it you could do this:

Dim wksSheet As Worksheet
Set wksSheet = Worksheets.Add
wksSheet.Name = "TransposedVB"

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

The new sheet is always the active sheet . so just do this
Sheets.Ad
ActiveSheet.Name = "TransposedVB

----- Dan Gesshel wrote: ----

Hello

I want to insert a sheet and then name it. That part is done, and not th
problem

Sheets.Ad
Sheets("Sheet1").Name = "TransposedVB

What happens is the code provides a specific name to the newly inserte
sheet. (In this case, Sheet1.
If the user deletes that sheet for whatever reason, while the workbook i
still open, when you add the next sheet it will be now named Sheet2, thu
breaking the code. The user would have to close the workbook and reopen i
to execute the code again and have it work

So, bascially what I'm looking for is the proper syntax (x + 1?) for addin
the new sheet each time Excel changes the Sheet name allowing me to renam
it. I know this is relatively easy, but I'm having a brain lapse at th
moment

Any help would be greatly appreciated

Thanks

Da

 

[Ron de Bruin]

Or in one step

On Error Resume Next
Sheets.Add.Name = "TransposedVB"
On Error GoTo 0

 

[Robin Hammond]

Dan,

If you use Sheets.Add, then the new sheet will be selected automatically, so
you can just use

ActiveSheet.Name = "TransposedVB"

Robin Hammond
www.enhanceddatasystems.com