vba add "space" at the and of the sheet' name if already exists

[tropis]

Hi,

Guys, code createsa copy of sheet “Main” and changes its name to a value from Sheet(1) cell C4.
Could youpls help me and give some advice how to check if the sheet with that namealready exists and if yes, display a msgbox “already exists” with two options: “abort”and “add”. When “add” is chosen, then the“space” (Chr(32)) should be added to the and of the name of the sheet (or twospaces when sheet with one already exists).
This iswhat I got so for:

Sub Adding()
Sheets("Main").Select
Sheets("Main").CopyAfter:=Sheets(2)
Sheets(3).Name= Sheets(1).Range("A4")
End Sub

Thank youin advance

 

[lrobbo314]

Something like this?

Sub Adding()
Dim Dict    As Object: Set Dict = CreateObject("Scripting.Dictionary")
Dim Main    As Worksheet: Set Main = Sheets("Main")
Dim sName   As String: sName = Main.Range("C4").Value
Dim ws      As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    Dict.Add ws.Name, ws.Name
Next ws


If Dict.exists(sName) Then
    If MsgBox("Already Exists!" & vbLf & "Continue Adding Sheet?", vbCritical + vbYesNo) = vbYes Then
        Main.Copy after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = sName & Chr(32)
    End If
End If


End Sub

 

[tropis]

Something like this?

[/CODE]

Probably something like this but it doesn’t work It doesn’t create any sheet, it doesn’t give anymsgbox. Nothing is going on.
One more time.
Workbook contains 2 sheets – sheet(1)“Baza”, sheet(2) “Main”
I would like to make a copy of “Main”,place this copy always after sheet “Main” (so it will always be created as 3^rdsheet in the workbook), name this copy as a text in cell C4 in sheet(1).
If name already exists then as in previouspost.

 

[lrobbo314]

Try this one

Sub Adding()
Dim Dict    As Object: Set Dict = CreateObject("Scripting.Dictionary")
Dim Main    As Worksheet: Set Main = Sheets("Main")
Dim sName   As String: sName = Sheets("Baza").Range("C4").Value
Dim ws      As Worksheet




For Each ws In ActiveWorkbook.Worksheets
    Dict.Add ws.Name, ws.Name
Next ws


Main.Copy After:=Sheets(2)


If Dict.exists(sName) Then
    If MsgBox("Already Exists!" & vbLf & "Rename with Space?", vbCritical + vbYesNo) = vbYes Then
        Sheets(3).Name = sName & Chr(32)
    Else ' if they select no
        'do something else
    End If
End If


End Sub

 

[tropis]

Still no.
Now it makes a copy of "Main" and names it "Main1", "Main2"....
It still doesn't name a sheets acc. to cell C4 in sheet1

 

[lrobbo314]

I was a little confused by the post.

So, what this will do now is just. Create a copy of Main. If what is in cell C4 on "Baza" is already a sheet name, it will name it what is in C4 with a space. Otherwise, it will just name it what is in C4.

Sub Adding()
Dim Dict    As Object: Set Dict = CreateObject("Scripting.Dictionary")
Dim Main    As Worksheet: Set Main = Sheets("Main")
Dim sName   As String: sName = Sheets("Baza").Range("C4").Value
Dim ws      As Worksheet


For Each ws In ActiveWorkbook.Worksheets
    Dict.Add ws.Name, ws.Name
Next ws


Main.Copy After:=Sheets(2)


If Dict.exists(sName) Then
    Sheets(3).Name = sName & Chr(32)
Else
    Sheets(3).Name = sName
End If


End Sub

 

[tropis]

Sorry you got confused, it is probably because of myEnglish.

Now it works better.

Now it makes of copy of Main. If what is in cell C4 on"Baza" is already a sheet name, it will name it what is in C4 with aspace. Perfect.

But, if such a situation happens again (what is in cell C4on "Baza" is already a sheet name, and sheet name “C4 with a space”already exists) then it reports an error.

Is there any way to continue adding next spaces at the end ofthe name if name from C4 already exists and name from C4 with a space alreadyexists, and name from C4 with two spaces already exists ….??

Basically, I want to insert more sheets with the same namefrom C4, so each consecutive sheet should have one more space at the end in thename.