Macro sheet rename based on cell-CodePudding

I should run 3 times a day a report which is copying the tab from "base" file into the simple excel workbook. The names of the tabs should be re-named with actual date. E.g. 19/11/2021, second report should be rename 19/11/2021_v2 and the third one 19/11/2021_v3. All of the tabs from the past need to be in the file so the macro should check which tabs is existing and based on the history should be rename the actual sheet. Here it is a picture about the file:

enter image description here

As you can see now the overview tab should be renamed as 19-11_v3.

Here it is my code which can rename only the first and second dates but the v3 is not working.

Where did my mistake?

Sub RenameSheet()

Dim Sht                 As Worksheet
Dim NewSht              As Worksheet
Dim VBA_BlankBidSheet   As Worksheet
Dim newShtName          As String

Set Target = Range("d11")

Set VBA_BlankBidSheet = Sheets("Overview")

Application.DisplayAlerts = False

VBA_BlankBidSheet.Copy After:=ActiveSheet
Set NewSht = ActiveSheet

newShtName = Target

For Each Sht In ThisWorkbook.Sheets
    
    If Sht.Name = Target Then
        newShtName = Target & "_v2"
        
    ElseIf Sht.Name = Target & "_v2" Then
        newShtName = Target & "_v3"
                  
    End If

Next Sht

NewSht.Name = newShtName


Application.DisplayAlerts = False
Sheets("Overview").delete


End Sub

CodePudding user response：

Try the following:

Sub RenameSheet()

Dim Sht                 As Worksheet
Dim NewSht              As Worksheet
Dim VBA_BlankBidSheet   As Worksheet
Dim newShtName          As String

Set Target = Range("d11")

Set VBA_BlankBidSheet = Sheets("Overview")

Application.DisplayAlerts = False

Set VBA_BlankBidSheet = ActiveSheet


VBA_BlankBidSheet.Copy After:=VBA_BlankBidSheet
Set NewSht = ActiveSheet
NewSht.Name = Target  & "v3"

VBA_BlankBidSheet.Copy After:=VBA_BlankBidSheet
Set NewSht = ActiveSheet
NewSht.Name = Target  & "v2"

Application.DisplayAlerts = True
Sheets("Overview").delete

End Sub

CodePudding user response：

Meanwhile, I could solve the problem. Here it is the working code:

Sub RenameSheet()

Dim Sht                 As Worksheet
Dim NewSht              As Worksheet
Dim VBA_BlankBidSheet   As Worksheet
Dim newShtName          As String

Set target = Range("d11")

' modify to your sheet's name
 Set VBA_BlankBidSheet = Sheets("overview")

 Set NewSht = ActiveSheet

 ' you can change it to your needs, or add an InputBox to select the Sheet's      name
 newShtName = target

 For Each Sht In ThisWorkbook.Sheets
If Sht.Name = target Then
    newShtName = target & "_v2"
    
End If

 If Sht.Name = target & "_v2" Then
    newShtName = target & "_v3"
    
End If
    
 Next Sht

 NewSht.Name = newShtName

 End Sub