Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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) "Sean" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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 "Sean" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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 "Bob Phillips" wrote: 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) "Sean" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Sheet Exists Q
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) "Sean" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If the sheet exists...., then | Excel Programming | |||
If sheet exists, then... | Excel Programming | |||
Sheet name already exists | Excel Discussion (Misc queries) | |||
How can I know if a sheet exists ? | Excel Programming | |||
check if sheet exists | Excel Programming |