I'm trying to rename sheets in an active workbook based on the value in the sheet. The problem for me is that in Sheet1 the value is in cell B7, in the rest of the sheets (sheet2 thru sheet'x'), the value is in cell B6.
This code changes all the sheet name perfectly, except that Sheet1 has the value in B7, rather than B6 for the rest.
Here is the code I'm trying to modify. The Sheet1 part works fine, but then the Sheet2 to SheetX part comes along and changes Sheet1. How do I say Sheet2 to SheetX?
Thanks in advance,
Don
This code changes all the sheet name perfectly, except that Sheet1 has the value in B7, rather than B6 for the rest.
Here is the code I'm trying to modify. The Sheet1 part works fine, but then the Sheet2 to SheetX part comes along and changes Sheet1. How do I say Sheet2 to SheetX?
VBA Code:
Option Explicit
Sub RenameSheet()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
'Just change sheet name for Sheet1
If Sheets(1).name = "Sheet1" Then
ws.name = ws.Range("B7").Value
End If
'From Sheet2 to SheetX.....
If Len(ws.Range("B6")) > 0 Then
ws.name = ws.Range("B6").Value
End If
On Error GoTo 0
If ws.name <> ws.Range("B6").Value Then
MsgBox ws.name & " Was Not renamed, the suggested name was invalid"
End If
Next
End Sub
Thanks in advance,
Don