Check if sheet name exists and add integer if yes

[Justplainj]

Hi All,

Apologies if I do not explain correctly.
I am struggling to figure out the logical flow of a portion of my code.

Part of my code I am busy with backs up the sheet with data that I have and renames it to a date value in a cell. (the date is time stamped from the previous time the code ran)

Example of this.
If I ran the code on the 20th of Sept 2022, then it will time stamp the updated data in cell M1 with 20-09-22 (DD-MM-YY)
If I run the code today, it will first create a copy of the data sheet and rename it to the date in M1 with the word Backup after the date and add the integer 1

I achieve this rename with the following code with

Dim X As Integer
X = 1
With ActiveSheet
    .Name = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
End With

However from recent it sometimes gets updated 3 or 4 times a day now, and before the date in M1 is today's date via the NOW() code the sheet name already exists and it gives an error.

What i am trying to achieve is to loop through the sheets and find if the sheet name matches in example 20-09-22 Backup1 and then +1 to X, making X = 2
Then it will check again in example 20-09-22 Backup2 and if it matches an existing sheet then + X with 1 again, making X = 3, etc and loops through until it finds a number 1, 2, 3, 4, etc that does not match an existing sheet name and then name the sheet with the date and the word Backup & a number.

I tried the following but it does not seem to work.

Dim X As Integer
Dim sht As Worksheet
X = 1
For Each sht In ThisWorkbook.Worksheets
    If ActiveSheet.Name <> Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X Then
        With ActiveSheet
            .Name = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
        End With
    Else
    X = X + 1
Next sht

Thanks for the help
J

 

[Alex Blakenburg]

Something like this might work for you:

Sub FindFreeBackupNo()

Dim X As Integer
Dim NewName As String

X = 1
NewName = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X

Do Until Not Evaluate("isref('" & NewName & "'!A1)")
    X = X + 1
    NewName = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
Loop

With ActiveSheet
    .Name = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
End With

End Sub

 

[dave3009]

A suggestion might be to use a full timestamp

.Name = Format(Now, "dd-mm-yy_hhmm")

Which would result in 27-09-22_0850

 

[Justplainj]

Hi Alex, Hi dave3009

Both of your solutions worked.
I also found the following post VBA check if sheet exists, if yes: select. If no select another sheet name
from the above post and from Alex I ended up using the following code.

Similar to Alex's code but I used a Do While instead of Do Until Not.

Dim X as Integer
Dim sht as String

Do While sht <> Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
sht = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
If Evaluate("isref('" & ShtName & "'!A1)") Then
   'sheet exists do something
   X = X + 1
Else
   'sheet doesn't exist do something else
    With ActiveSheet
        .Name = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
    End With
End If
Loop

Thanks for all the help.
J

 

[Justplainj]

Hi Alex, Hi dave3009

Both of your solutions worked.
I also found the following post VBA check if sheet exists, if yes: select. If no select another sheet name
from the above post and from Alex I ended up using the following code.

Similar to Alex's code but I used a Do While instead of Do Until Not.

Dim X as Integer
Dim sht as String

Do While sht <> Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
sht = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
If Evaluate("isref('" & ShtName & "'!A1)") Then
   'sheet exists do something
   X = X + 1
Else
   'sheet doesn't exist do something else
    With ActiveSheet
        .Name = Format(ActiveSheet.Range("M1").Value, ("DD-MM-YY")) & " Backup" & X
    End With
End If
Loop

Thanks for all the help.
J

Edit on the following line

If Evaluate("isref('" & ShtName & "'!A1)") Then

should be

If Evaluate("isref('" & sht & "'!A1)") Then

if the code above is copied as is as the Dim I used is sht and not ShtName

 

[Alex Blakenburg]

Thanks for the feedback.
The test is a rather strange way of doing it but it does work. In the code you sent back you did not have your previous X = 1 initialisation step so you know your first Backup is going to be Backup0 not Backup1 right ?

 

[smozgur]

Although the final code works (with the fix in the next post by the way), I switched the marked solution post to help future readers since the suggested method by @Alex Blakenburg certainly is the way to go for such a problem and also written very well.