PDA

View Full Version : determine if a sheet exist



lior03
12-26-2005, 04:42 AM
hello
how do i determine if a sheet name "cc" exist in a workbook?.
namely if it is then do so & so if it's not then create one.
thanks

Bob Phillips
12-26-2005, 05:01 AM
If Not SheetExists("cc") Then
Worksheets.Add.Name = "cc"
End If

'-----------------------------------------------------------------
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 = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

lior03
12-26-2005, 05:32 AM
hello
whats wrong with

Sub targill()
Application.ScreenUpdating = False
If Not SheetExists("cc") Then
Worksheets.Add.Name = "cc"
Exit Sub
Else
Sheets("cc").Delete
Dim newsheet
Worksheets("projects").Range("A1:C4000").Copy
Set newsheet = Worksheets.Add
newsheet.Range("A1").PasteSpecial paste:=xlValues
ActiveSheet.Name = "cc"
Application.ScreenUpdating = True
End If
End Sub

Norie
12-26-2005, 09:00 AM
Could you tell us what the problem is?:)

Shazam
12-26-2005, 11:15 AM
Is this what you're after ?



Sub Test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name = "CC" Then ws.UsedRange.Clear
Next ws
On Error Resume Next
If IsError(Sheets("CC").Select) Then Sheets.Add
ActiveSheet.Name = "CC"
On Error GoTo 0
Worksheets("projects").Range("A1:C4000").Copy
Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Cyberdude
12-26-2005, 12:06 PM
I always used something like:
On Error Resume Next
Worksheets("Main").Select
If Err <> 0 Then MsgBox "Sheet does not exist."
On Error GoTo 0