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"

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"

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"

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"

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"

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"

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"