Name Worksheet
I have a macro that creates a new worksheet when I run it. The given name
could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
hi,
When you add a sheet it becomes the activesheet so use that Worksheets.Add ActiveSheet.Name = "Remaining" Mike "MCheru" wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
Just remember to find the sheet that's CURRENTLY named "remaining" and change
it's name first. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Mike H" wrote: hi, When you add a sheet it becomes the activesheet so use that Worksheets.Add ActiveSheet.Name = "Remaining" Mike "MCheru" wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
You can name the worksheet when you create it.
ThisWorkbook.Worksheets.Add().Name = "Remaining" Or, since creating a sheet makes it the active sheet, you could use ThisWorkbook.Worksheets.Add ActiveSheet.Name = "Remaining" If you really want to use a loop, try Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(Left(WS.Name, 5), "Sheet", vbTextCompare) = 0 Then WS.Name = "Remaining" Exit For End If Next WS Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 27 Mar 2009 12:51:01 -0700, MCheru wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
Terrific! Thank you for you're help!
"Mike H" wrote: hi, When you add a sheet it becomes the activesheet so use that Worksheets.Add ActiveSheet.Name = "Remaining" Mike "MCheru" wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
Okay, I'll keep that in mind.
"JBeaucaire" wrote: Just remember to find the sheet that's CURRENTLY named "remaining" and change it's name first. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Mike H" wrote: hi, When you add a sheet it becomes the activesheet so use that Worksheets.Add ActiveSheet.Name = "Remaining" Mike "MCheru" wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
Name Worksheet
Absolutely charming. This is magnificent. Thank you!
"Chip Pearson" wrote: You can name the worksheet when you create it. ThisWorkbook.Worksheets.Add().Name = "Remaining" Or, since creating a sheet makes it the active sheet, you could use ThisWorkbook.Worksheets.Add ActiveSheet.Name = "Remaining" If you really want to use a loop, try Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(Left(WS.Name, 5), "Sheet", vbTextCompare) = 0 Then WS.Name = "Remaining" Exit For End If Next WS Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 27 Mar 2009 12:51:01 -0700, MCheru wrote: I have a macro that creates a new worksheet when I run it. The given name could be Sheet 1, Sheet 2, Sheet 3, etc. The point is that I never know what number Sheet the macro will give the worksheet because it changes each time I run it. I want to create a code that will find the worksheet with Sheet in the name and change the name of the worksheet to Remaining. Here is what I have so far. newsht.Name = "Remaining" |
All times are GMT +1. The time now is 07:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com