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

 

[Snakehips]

@KlausW Maybe you could utilise the Worksheet_Change event by having like below in the VPL sheet's code module?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewName As String
Dim OldName As String
Dim OSet As Integer

If Intersect(Target, Range("A2:B60")) Is Nothing Then Exit Sub  'Edit range to suit

If Target.Count > 1 Then Exit Sub  'Ignore if not a single cell entry
OSet = 3  'entry in B gets name in E
If Target.Column = 1 Then OSet = 1   'if entry in A gets name in B
OldName = Target.Offset(0, OSet).Text
NewName = Target.Text

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

HTH

 

[abdelfattah]

@KlausW
I'm not sure If I can help you, but I sugget for you to post your details before and after using XL2BB tools , maybe the others members could help you.

 

[KlausW]

@Snakehips I can't get it to Work, I opload some Picture to illustration. Hope that will help. KW

 

[KlausW]

@KlausW
I'm not sure If I can help you, but I sugget for you to post your details before and after using XL2BB tools , maybe the others members could help you.

Hi abdelfattah, I'm not very strong in using XL2BB tools, but maybe I can learn that.
Can my uploaded images be used?
KW

 

[Snakehips]

@KlausW My code should enable exactly that.

Where do you have the code?
Within the Project pane to the left of the VBA Editor, double click on the 'VPL' sheet.
That will ope the VPL sheets code pane.
That is where you need to paste the code.

The code should then run as and when you enter your numbers in column B then A and rename the name sheet accordingly.

 

[KlausW]

Hi Snakehips as it should be. Thank you. I have an additional question. Can I get it to run after I run another VBA code. Which I run using a button?.

Best Regards Klaus W

The code it must run after looks like this and it is still in Sheet VPL.

Sub Rektangelafrundedehjørner4_Klik()

Dim ws As Worksheet
Dim wbk As Workbook
Dim Pth As String, Fname As String
Pth = Range("m1") 'GetFolder()
Fname = Dir(Pth & "\*.xls*")

Do While Fname <> ""
Set wbk = Workbooks.Open(Pth & "\" & Fname)
For Each ws In wbk.Worksheets
If Not ShtExists(ws.Name, ThisWorkbook) Then
ws.Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next ws
wbk.Close False
Fname = Dir
Loop

'Sheets("Fast besætning").Select

End Sub

 

[Snakehips]

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.

 

[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 I can't get the last code to run. But the first one you posted is running, can it be coded to a button? Best Regards Klaus W

 

[Snakehips]

Hi Snakehips I can't get the last code to run. But the first one you posted is running, can it be coded to a button? Best Regards Klaus W

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.