Rename sheets 2 times from name from another shhet by vba

[KlausW]

Hello everyone I have a challenge naming tabs that I import into a sheet. The tab in which the name appears is called VPL, in column E there are names of the employees, and that is the name of the tabs that I import as well. In column B I insert a number, and now the tabs must change to the number to the left of the name. Example tje name Peter is in E5 and so is the sheet called Now I write 4205 in B5, the sheet name Peter must also be renamed to 4205. Later I write 199 in A5, the sheet 4205 must also be renamed to 199. There are about 50 Tabs. Does it make sense. Any help will be appreciated. Best Regards Klaus W

 

[KlausW]

With the first code disabled and the second code in the VPL sheet's code pane, second code should happily be run from a button within the VPL sheet?
It just requires that at the time of running it, a cell in the relevant row in VPL sheet is selected.
It needs that, or some by some other means to know which row it is referring to for data.
The first code knows which row because it is because it is associated/triggered by the Change Event.

Hi Snakehips Im on work in Greenland right now and the net is terribal. So I replay ASAP it return to normale. Regards Klaus W

 

[KlausW]

Når den første kode er deaktiveret og den anden kode i VPL-arkets koderude, skal anden kode med glæde køres fra en knap i VPL-arket?
Det kræver bare, at der på tidspunktet for kørsel vælges en celle i den relevante række i VPL-ark.
Det har brug for det, eller på anden måde for at vide, hvilken række det henviser til for data.
Den første kode ved hvilken række, fordi den skyldes, at den er tilknyttet/udløst af ændringshændelsen.

With the first code disabled and the second code in the VPL sheet's code pane, second code should happily be run from a button within the VPL sheet?
It just requires that at the time of running it, a cell in the relevant row in VPL sheet is selected.
It needs that, or some by some other means to know which row it is referring to for data.
The first code knows which row because it is because it is associated/triggered by the Change Event.

I would like to have a button that I can press, also the VBA code should run

 

[KlausW]

Hi Klaus.
I'm not entirely sure what your method of working is but this might help?

Sub ReNameTab()
Dim NewName As String
Dim OldName As String
Dim Rw As Integer
Rw = Selection.Row  '***Assumes cell within relevant row in VPL will be selected?????

'If A has value, rename Tab B as  A
If Len(Cells(Rw, 1).Text) > 1 Then
    OldName = Cells(Rw, 2)
    NewName = Cells(Rw, 1)
    GoTo DoStuff
Else
'Otherwise If B has value, rename Tab E as B
If Len(Cells(Rw, 2).Text) > 1 Then
    OldName = Cells(Rw, 5)
    NewName = Cells(Rw, 2)
End If
End If

DoStuff:
On Error Resume Next  ' ignore if sheet name cannot be found
'Change tab name
Sheets(OldName).Name = NewName
On Error GoTo 0  ' reset default error handling
End Sub

As it stands, it is reliant upon having a cell within the appropriate row of VPL selected at the time of running.

Hi Snakehips working as it should, thanks a lot. I will make a new question, on the one with the button. Best regards Klaus W

 

[smozgur]

Hi Snakehips working as it should, thanks a lot. I will make a new question, on the one with the button. Best regards Klaus W

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.