VBA to rename worksheets depending on selection in drop down menu

[Sunny London]

I have worksheets called "Sales FX1" and "Payments FX1".

In my "Data Input" worksheet, cell B41 is driven by a drop down menu with options to choose different currencies "Euro", "GBP", "Yen" etc.

Depending which currency is chosen, is there VBA code that would rename the worksheets e.g. "Sales Euro" and "Payments Euro" or "Sales GBP" and "Payments GBP" as appropriate?

Thanks

 

[jim may]

In its simplist form post this code into your "DataInput" Sheet Code window
Assumes worksheet layout (order left to right) DataInput, Sales FX1, Payments FX1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SalesWs As Worksheet, PaymentsWs As Worksheet
If Target.Cells <> Range("B41") Then
Exit Sub
Else
Worksheets(2).Name = "Sales " & Target.Value
Worksheets(3).Name = "Payments " & Target.Value
End If
End Sub

 

[ogo]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 41 Then
myValue = Cells(41, 2).Value
For Each Sheet In Sheets
    Sheet.Activate
    If Not ActiveSheet.Name = "Data Input" Then
        sheetName = ActiveSheet.Name
        pstn = InStr(sheetName, " ")
        NewName = Left(sheetName, pstn) & Target.Value
        ActiveSheet.Name = NewName
    End If
Next
End If
ActiveWorkbook.Sheets("Data Input").Select
End Sub

Thanks,
Ogo

 

[Sunny London]

Thanks. But I need a bit more help.

I'm trying to automatically rename 2 sets of 3 worksheets - adding the code to an exisiting macro which shows or hides tabs depending on the results of cells containing 2 different drop down menus, B41 and B44. How should I add your new renaming code to the existing macro below? If the user changes the dropdown selection in cell B41 from say, "Yen" to "Euro" and the dropdown selection in B44 from, say "GBP" to blank, then the macro needs to be able to change the name of the worksheet or, if blank is selected, change the name back to "Sales FX1" or "Recpt FX1" or "Payts FX1" and then hide the worksheet again.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B41" Then
Sheets("Sales FX1").Visible = Target.Value <> ""
Sheets("Recpt FX1").Visible = Target.Value <> ""
Sheets("Payts FX1").Visible = Target.Value <> ""
ElseIf Target.Address(False, False) = "B44" Then
Sheets("Sales FX2").Visible = Target.Value <> ""
Sheets("Recpt FX2").Visible = Target.Value <> ""
Sheets("Payts FX2").Visible = Target.Value <> ""
End If
End Sub

Thanks.