Worksheets.Add Problem 1

[krzysrb]

Hey guys,

I am running this code in my app to insert another worksheet into workbook:

Set ExcelWS = ExcelWB.worksheets.Add
ExcelWS.Name = "Created on " & Format(Now, "MMM-d&quot & " at " & Format(Now, "H-mm&quot

The name part is supposed to give it somewhat unique name, so if the user runs it repeatevly at least they are different in the workbook.

My problem is that whenever I add the worksheet this way, it gets inserted at the [number of default sheets in excel workbook] + 1 position, which is ok with me, and every other time I insert into this workbook, it replaces the previously inserted one. For the purpose of my app it is crucial that all the worksheets stay in the same workboook. Is there any way to add the worksheet without replacing the previously inserted one?

Thank you all for your help
vlada

 

[VBAva]

hello

i gave this a quick test and i think it does what you are after.
just one suggestion, if you try to create more than one new sheet within the same minute you get an error, so if this is likely to happen maybe you should put in something else to change the sheet name slightly so the error does not occur


Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.name = "Created on " & Format(Now, "MMM-d&quot & " at " & Format(Now, "H-mm&quot

 

[VBAva]

there is probably a better way of doing this but i just did it out quickly ( i have not been working with VBA for long so my coding is probably wrong)
it seems to work fine, this is the result
Created on Aug-27 at 9-07 sheet is created, if you try to create another sheet at the same time it names the new one
Created on Aug-27 at 9-07[1] then
Created on Aug-27 at 9-07[2]
etc


Sub addingSheet()

Dim NewSheetName As String
Dim shtName As Worksheet
Dim NameOk As Boolean
Dim i As Integer
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
NewSheetName = "Created on " & Format(Now, "MMM-d&quot & " at " & Format(Now, "H-mm&quot

i = 0

Do
NameOk = True
For Each shtName In Application.Worksheets 'Checks all the sheets
If shtName.name = NewSheetName Then 'If sheet is already there
MsgBox "Sheet exists already"
NameOk = False
i = i + 1
NewSheetName = "Created on " & Format(Now, "MMM-d&quot & " at " & Format(Now, "H-mm&quot & "[" & i & "]"
Exit For
End If
Next shtName 'Check the next sheet
Loop While NameOk = False

ActiveSheet.name = NewSheetName
end sub

 

[krzysrb]

VBAva,

You were more than right my friend, it certainly does what it is supposed to, and I have no idea why it behaved so ugly last night. Thank you for your help. I was doubting between two ways of how to battle the 'same name sheet' error, and one of the ways to solve it is yours, and a simpler one (and sloppier too ) is mine.

ExcelWS.Name = "Created on " & Format(Now, "MMM-d&quot & " at " & Format(Now, "H.mm.ss&quot

With this, I just add the seconds part (.ss) to the sheet name, and they are always unique.

I appreciate the time you took to help me out.
Vlada

 

[VBAva]

Hi,

no problem. i sometimes get bored in work so its more interesting to work on other peoples problems, and it helps the learning process.
i know my way is a little long but hey, i was bored, and i think it looks nice

thanks for the star