Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

rename sheetname based on value of field in other sheet

Folks

How based on an input value from field b1 in sheet 1 can a macro change the name of a excel sheet  i.e. rename to "chosen continent"

if
sheet 1 b1  = europe
 
rename sheet 2 to "chosen continent"
rename sheet 3 to "americas"
rename sheet 4 to "asia"

if
sheet 1 b1  = americas
rename sheet 2 to "europe"
rename sheet 3 to "chosen continent"
rename sheet 4 to "asia"

if
sheet 1 b1  = asia
 
rename sheet 2 to "europe"
rename sheet 3 to "americas"
rename sheet 3 to "chosen continent"
0
rutgermons
Asked:
rutgermons
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
Here is the VBA that follows your pseudo code.

Sub test()
   RenameSheet (Range("B1").value)
End Sub


Sub RenameSheet(value As String)
   If (value = "europe") Then
      Sheets(2).Name = "chosen continent"
      Sheets(3).Name = "americas"
      Sheets(4).Name = "asia"
   ElseIf (value = "americas") Then
      Sheets(2).Name = "europe"
      Sheets(3).Name = "chosen continent"
      Sheets(4).Name = "asia"
   ElseIf (value = "asia") Then
      Sheets(2).Name = "europe"
      Sheets(3).Name = "americas"
      Sheets(4).Name = "chosen continent"
   End If
End Sub

Open in new window

0
 
byundtCommented:
If you want the renaming to occur automatically, then consider a Worksheet_Change sub in the code pane for Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
    On Error Resume Next
    Worksheets("chosen continent").Name = Array(, , "europe", "americas", "asia")(Worksheets("chosen continent").Index)
    Worksheets(Range("B1").Value).Name = "chosen continent"
    On Error GoTo 0
End If
End Sub

Open in new window

If instead, you want a simple macro for renaming (and don't need all the error handling provisions), then consider this two liner:
Sub SheetRenamer()
Worksheets("chosen continent").Name = Array(, , "europe", "americas", "asia")(Worksheets("chosen continent").Index)
Worksheets(Worksheets("Sheet1").Range("B1").Value).Name = "chosen continent"
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now