Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
Dim NewSheet As WorkSheet
Set NewSheet = Workbook.Sheets.Add NewSheet.Name = "TransposedVB" "Dan Gesshel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
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 * "Dan Gesshel" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
Or in one step
On Error Resume Next Sheets.Add.Name = "TransposedVB" On Error GoTo 0 -- Regards Ron de Bruin http://www.rondebruin.nl "chris" wrote in message ... The new sheet is always the active sheet . so just do this: Sheets.Add ActiveSheet.Name = "TransposedVB" ----- Dan Gesshel wrote: ----- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Sheets and Naming
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 "Dan Gesshel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a quick way of inserting and naming multiple worksheets in Excel? | Excel Worksheet Functions | |||
Naming sheets from a cell value | Excel Discussion (Misc queries) | |||
VBA Help naming sheets | Excel Discussion (Misc queries) | |||
Naming Sheets for day of the month | Excel Discussion (Misc queries) |