Solved: Add sheet & name

[vzachin]

hi,

i currently name a sheet by referencing 2 ranges, however if the sheetname already exists, then this will fail of course.

how can i save any subsequent sheets?
my thought would be to append an incrementing number but i don't know how to write the code.

this is what i have so far
[vba]
Sub SaveSheet()
Sheets("test").Select
Sheets("test").Copy After:=Sheets("test")
Sheets("test (2)").Select
PCode = Sheets("test (2)").Range("A1")
Ver = Sheets("test (2)").Range("A2")
Sheets("test (2)").Name = Ver & "~" & PCode
Sheets(Ver & "~" & PCode).Tab.ColorIndex = 5
End Sub

[/vba]


thanks
zach

[Bob Phillips]

[vba]

Dim fOK As Boolean
Dim SheetName As String
Dim Inc As Long

SheetName = "Report"
If SheetExists(SheetName) Then

Do

Inc = Inc + 1
SheetName = "Report (" & Inc & ")"
fOK = Not SheetExists(SheetName)
Loop Until fOK
End If
Worksheets.Add.Name = SheetName



'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = Not wb.Worksheets(Sh) Is Nothing
On Error GoTo 0
End Function
[/vba]

[Simon Lloyd]

Try something like this:
[VBA]Sub SaveSheet()
Dim PCode As Range, Ver As Range
Set PCode = Sheets("test").Range("A1")
Set Ver = Sheets("test").Range("A2")
Sheets("test").Copy After:=Sheets("test")
If Sheets(Ver.Text & " - " & PCode.Value) Is Nothing Then
ActiveSheet.Name = Ver.Text & " - " & PCode.Value
ActiveSheet.Tab.ColorIndex = 5
Else
Sheets("test").Range("A1").Value = Sheets("test").Range("A1").Value + 1
ActiveSheet.Name = Ver.Text & " - " & PCode.Value
ActiveSheet.Tab.ColorIndex = 5
End If
End Sub[/VBA]

[david000]

http://www.mrexcel.com/podcast/2008_04_01_archive.html

Number: 730

[vzachin]

xld,

i'm not able to get this to work correctly.i don't understand how to reference A1 & A2.

Simon,
if the sheet does not exist, i get "subscript out of range" here:
[vba]
If Sheets(Ver.Text & " - " & PCode.Value) Is Nothing Then
[/vba]
and if the sheet exists, i get Run-time error 13 : Type mismatch[vba]Sheets("test").Range("A1").Value = Sheets("test").Range("A1").Value + 1[/vba]


zach

[vzachin]

david,
couldn't view the pod...

zach

[david000]

OK, sorry you can't get that link to work...


Here is the code from the show! (attachment)

[rangudu_2008]

Chk out whether this code wud help u out:

http://www.erlandsendata.no/english/...batextexportwb

[Simon Lloyd]

As long as you used my code in its entirity it works fine! it worked for me ok.

[Bob Phillips]

xld,

i'm not able to get this to work correctly.i don't understand how to reference A1 & A2.

Same way that you did before, just build it up piecemeal and pass to the function

[vba]

SheetName = Sheets("test (2)").Range("A1") & "~" & Sheets("test (2)").Range("A2")
If SheetExists(SheetName) Then

Do

Inc = Inc + 1
SheetName = "Report (" & Inc & ")"
fOK = Not SheetExists(SheetName)
Loop Until fOK
End If
Worksheets.Add.Name = SheetName
[/vba]

[vzachin]

hi simon,

i tried your code again and still no good. in any event, xld's code works

thanks again
zach

[vzachin]

hi XLD,

thanks again. works for me now

zach

[vzachin]

david & rangadu,

thanks for the links. it didn't work out for me but i'm sure i can use them in the future

zach