Naming a newly inserted sheet using VBA
I want to insert a new sheet and name it "ABC":
Sheets.Add Sheets("Sheet1").Name = "ABC" This is fine for the first time as it inserts a new sheet called "Sheet1". However the next time I do it the newly inserted sheet is called "Sheet2", but the code is still looking for a "Sheet1". Can I do something simple like: Sheets.Add.Name = "ABC" Thanks in advance. |
What happened when you tried it?
(It worked for me.) Ant wrote: I want to insert a new sheet and name it "ABC": Sheets.Add Sheets("Sheet1").Name = "ABC" This is fine for the first time as it inserts a new sheet called "Sheet1". However the next time I do it the newly inserted sheet is called "Sheet2", but the code is still looking for a "Sheet1". Can I do something simple like: Sheets.Add.Name = "ABC" Thanks in advance. -- Dave Peterson |
Yes it does work the first time, but Excel automatically calls the second
sheet "Sheet2" in the workbook so it will debug as it will be looking for "Sheet1". Actually I have just tried the following and it seemed to work... Sheets.Add Dim oSheet As Worksheet Dim sName As String sName = "ABC" Set oSheet = Application.ActiveSheet oSheet.Name = sName "Dave Peterson" wrote: What happened when you tried it? (It worked for me.) Ant wrote: I want to insert a new sheet and name it "ABC": Sheets.Add Sheets("Sheet1").Name = "ABC" This is fine for the first time as it inserts a new sheet called "Sheet1". However the next time I do it the newly inserted sheet is called "Sheet2", but the code is still looking for a "Sheet1". Can I do something simple like: Sheets.Add.Name = "ABC" Thanks in advance. -- Dave Peterson |
All times are GMT +1. The time now is 02:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com