PDA

View Full Version : Solved: Add sheet & name



vzachin
05-09-2008, 05:22 AM
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

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




thanks
zach

Bob Phillips
05-09-2008, 06:20 AM
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

Simon Lloyd
05-09-2008, 06:54 AM
Try something like this:
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

david000
05-09-2008, 12:13 PM
http://www.mrexcel.com/podcast/2008_04_01_archive.html

Number: 730

vzachin
05-09-2008, 08:12 PM
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:

If Sheets(Ver.Text & " - " & PCode.Value) Is Nothing Then

and if the sheet exists, i get Run-time error 13 : Type mismatchSheets("test").Range("A1").Value = Sheets("test").Range("A1").Value + 1


zach

vzachin
05-09-2008, 08:18 PM
david,
couldn't view the pod...

zach

david000
05-09-2008, 08:25 PM
OK, sorry you can't get that link to work...:(


Here is the code from the show! (attachment):friends:

rangudu_2008
05-09-2008, 08:44 PM
Chk out whether this code wud help u out:

http://www.erlandsendata.no/english/index.php?d=envbatextexportwb

Simon Lloyd
05-09-2008, 11:19 PM
As long as you used my code in its entirity it works fine! it worked for me ok.

Bob Phillips
05-10-2008, 01:40 AM
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



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

vzachin
05-10-2008, 06:41 PM
hi simon,

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

thanks again
zach

vzachin
05-10-2008, 06:42 PM
hi XLD,

thanks again. works for me now

zach

vzachin
05-10-2008, 06:44 PM
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