If Sheet Exists Q

[Sean]

I have the following code, that is trying to copy the sheet Log, right
of the active sheet but only IF it does not exist already.

I am getting a Sub not defined on line "If SheetExists(newShtName)
Then" and don't know why.

The sheet who's value in A5 is 09/03/08 does not actually exist, so it
should copy.

Any help appreciated

Sub CreateYTD_PostLog()
Dim shtName As String
Dim newShtName As String
Dim WS As Worksheet
Application.ScreenUpdating = False

ActiveWindow.DisplayHeadings = False

For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="123"
Next WS

Sheets("Log").Activate
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")

If SheetExists(newShtName) Then
MsgBox "You have already created this week.", vbCritical
Exit Sub
End If
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
ActiveSheet.Tab.ColorIndex = -4142
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
Sheets(shtName).Activate

Sheets("Log").Select
ActiveSheet.Unprotect Password:="123"
range("A1").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("Log").Select
range("A1").Select
End Sub

 

[Bob Phillips]

Probably because the sub SheetExists does not exist!

Add this to your code

'-----------------------------------------------------------------
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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Sean,

You can't invent a function like SheetExists, you must write it or do
something different. Hers an alternative:-

For Each WS In Worksheets
If WS.Name = newShtName Then flg = True: Exit For
Next
If flg = True Then
Else
Sheets.Add.Name = newShtName
End If

If you include this you'll then find several other errors in your code
You try to rename a sheet to the same name as an existing sheet.
You try to select the 'Log' worksheets after you have just hidden it.
You can't do either of those things.

Mike

Mike

 

[john]

As Mike H & Bob already pointed out you are missing function and also, there
are errors in your code which Mike has also mentioned.
I have quickly played with it a bit and hopefully it will work as intended

not tested:

Sub CreateYTD_PostLog()
Dim shtName As String
Dim newShtName As String
Dim WS As Worksheet
Application.ScreenUpdating = False

ActiveWindow.DisplayHeadings = False

For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="123"
Next WS

On Error Resume Next
With Worksheets("Log")
.Activate
shtName = .Name
newShtName = Format(.Range("a5").Value, "dd-mm-yy")

If SheetExists(newShtName) Then
MsgBox "You have already created this week.", vbCritical
Exit Sub
Else
.Copy after:=ActiveSheet
End If
End With

With ActiveSheet
.Name = newShtName
.Tab.ColorIndex = -4142
.DrawingObjects.Visible = True
.DrawingObjects.Delete
End With
On Error GoTo 0

Sheets(shtName).Activate

Sheets("Log").Select
ActiveSheet.Unprotect Password:="123"
Range("A1").Select

ActiveWindow.SelectedSheets.Visible = False

End Sub

Function SheetExists(sName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(sName).Name))
On Error GoTo 0
End Function

--
JB

Probably because the sub SheetExists does not exist!

Add this to your code

'-----------------------------------------------------------------
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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have the following code, that is trying to copy the sheet Log, right
of the active sheet but only IF it does not exist already.

I am getting a Sub not defined on line "If SheetExists(newShtName)
Then" and don't know why.

The sheet who's value in A5 is 09/03/08 does not actually exist, so it
should copy.

Any help appreciated

Sub CreateYTD_PostLog()
Dim shtName As String
Dim newShtName As String
Dim WS As Worksheet
Application.ScreenUpdating = False

ActiveWindow.DisplayHeadings = False

For Each WS In ThisWorkbook.Worksheets
WS.Unprotect Password:="123"
Next WS

Sheets("Log").Activate
shtName = ActiveSheet.Name
newShtName = Format([a5], "dd-mm-yy")

If SheetExists(newShtName) Then
MsgBox "You have already created this week.", vbCritical
Exit Sub
End If
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = newShtName
ActiveSheet.Tab.ColorIndex = -4142
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
Sheets(shtName).Activate

Sheets("Log").Select
ActiveSheet.Unprotect Password:="123"
range("A1").Select

ActiveWindow.SelectedSheets.Visible = False

Sheets("Log").Select
range("A1").Select
End Sub

 

[Sean]

Right again Bob! I copied from another piece of code I had and never
copied this

Private Function SheetExists(sname) As Boolean
'from John Walkenbach
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

Q - How it seems to work (which is not how I want it to work) is If
the sheet exists it just copies to a new sheet and re-names as "log
(2)". What I thought my code did, is take the value of A5 and see if a
sheet name exists of that, if it does exist - show a msb box, if it
does,'t create a new copy sheet called - the value in A5 (in format)
dd-mm-yy.

 

[Sean]

Thanks Guys, I've made a general 'dogs dinner' of it, when I thought a
nice handy short-cut of copying another similar piece of code would do
nicely.

Guess there are no real short-cuts!

 

[Bob Phillips]

From my reading of the code it is not doing what you say, but it does
process a sheet called Log.

This code

newShtName = Format([a5], "dd-mm-yy")

is very bad code IMO. By using short cut range notation, it ALWAYS works on
the activesheet. This may be what is required, but far batter to be explicit
so everyone knows

newShtName = Format(Activesheet.range("A5").Value, "dd-mm-yy")

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)