Rename worksheets

[Patrick C. Simonds]

Any way to get this to work? I want the worksheets to rename based on the
values or cells F1 and G1 of each worksheet

Sub Rename_Worksheets()
'
' Macro1 Macro
' Macro recorded 12/19/2005 by Cathy Baker
'

'

'This code runs to rename the worksheets

Dim wks As String
Dim Sh As Worksheet

'Application.ScreenUpdating = False

wks = ActiveSheet.Name

Const sStr As String = "F1 " & "G1"

On Error GoTo ErrHandler
For Each Sh In ThisWorkbook.Worksheets
Sh.Name = Sh.Range(sStr).Value
Next Sh

Worksheets(wks).Activate

Exit Sub
ErrHandler:
'MsgBox "Cell" & sStr & "on sheet" & sh.Name & "is not valid sheet name"
Resume Next

'Application.ScreenUpdating = True
End Sub

 

[Patrick C. Simonds]

Well I have a partial solution to my problem in the form of:

=TEXT(F1,"General") & TEXT(G1," General")

Is there any way I can cause a space between the two results when it is
displayed. Currently it looks like 2009Qrt2. I would like 2009 Qrt2.

 

[Bob Bridges]

Sure, you can make it work, Patrick. The basic structure looks ok, but I see
some errors in particular statements...well, on second thought only one
actual error: Your constant sStr comes across here as two cell addresses
with a space between them, which doesn't really mean anything when used with
Range further down (Sh.Range("F1 G1").Value). But I'm not sure how you need
to fix it. You say you need the new worksheet name to be based on "F1 and
G1"; do you mean the two values concatenated? If so, your assignment
statement would have to be something like this:

Sh.Name = Sh.Range("F1").Value & Sh.Range("G1").Value

Or if you need a space between the two values, of course,

Sh.Name = Sh.Range("F1").Value & " " & Sh.Range("G1").Value

There are a few things I think you're doing unnecessarily, but they're not
errors. For example, you save the name of the active-sheet name up front and
then make sure that sheet is reactivated at the end. But since you never did
anything in your loop to activate any of the sheets you worked with -
renaming a worksheet doesn't activate it - you didn't need to save or restore
any active sheet; this logic doesn't change it at all.

(If you HAD needed to restore the active sheet, your way would work but I
think it'd be slightly simpler to do this:

Set wks = ActiveSheet
' Do your loop here
wks.Activate

That saves the actual sheet object rather than its name, you see. But it's
a detail; your way was fine.)

I THINK that if you change the one Sh.Name assignment, your program will
work correctly. If not, ask again. But here's how I'd simplify the code, if
you care:

Sub Rename_Worksheets()
On Error GoTo ErrHandler
For Each Sh In ThisWorkbook.Worksheets
vn = Sh.Range("F1").Value & Sh.Range("G1").Value
Sh.Name = vn
Next Sh
Exit Sub

ErrHandler:
MsgBox "Error while trying to rename sheet '" & Sh.Name & _
"'. Perhaps " & vn & " isn't a valid sheet name."
Resume Next

End Sub

 

[ShaneDevenshire]

Hi,

You could try

Dim sh As Worksheet
For Each sh In Worksheets
With sh
.Name = .[F1] & " " & .[G1]
End With
Next sh