Check if sheet name exists and add integer if yes

Justplainj

New Member
Joined
Apr 15, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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.

VBA Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Something like this might work for you:

VBA Code:
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
 
Upvote 0
Solution
A suggestion might be to use a full timestamp

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

Which would result in 27-09-22_0850
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
VBA Code:
If Evaluate("isref('" & ShtName & "'!A1)") Then
should be
VBA Code:
If Evaluate("isref('" & sht & "'!A1)") Then
if the code above is copied as is as the Dim I used is sht and not ShtName
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top