I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. There is an error
check that is supposed to flag the error that occurs if a "Working
Template" sheet already exists - then delete the current (newly added)
sheet and the "Working Template", then go back to the beginning. For
some reason, though, it's just not working at all. I wrote the code
on another computer and had no problems, but on this machine it
refuses to catch it. I could rewrite it to circumvent the problem,
but it is happening elsewhere too, so i would like to get a better
understanding of why.

Any advice or recommendations would be greatly appreciated.

Function ()
beginning:
Sheets.Add After:=Sheets(6)
On Error GoTo error_occured
Sheets(7).Name = "Working Template"
Sheets(6).Cells.Copy
Sheets(7).Select
With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Cells.UnMerge
Exit Function

error_occured:
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Working Template").Delete
Application.DisplayAlerts = True
Resume beginning
End Function

Thanks,
Thedude

"it's just not working at all"
The first thing to do is give the function a name.

From...
Function()
To...
Function Randolph_Scott()
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





wrote in message
I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. There is an error
check that is supposed to flag the error that occurs if a "Working
Template" sheet already exists - then delete the current (newly added)
sheet and the "Working Template", then go back to the beginning. For
some reason, though, it's just not working at all. I wrote the code
on another computer and had no problems, but on this machine it
refuses to catch it. I could rewrite it to circumvent the problem,
but it is happening elsewhere too, so i would like to get a better
understanding of why.

Any advice or recommendations would be greatly appreciated.

Function ()
beginning:
Sheets.Add After:=Sheets(6)
On Error GoTo error_occured
Sheets(7).Name = "Working Template"
Sheets(6).Cells.Copy
Sheets(7).Select
With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Cells.UnMerge
Exit Function

error_occured:
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Working Template").Delete
Application.DisplayAlerts = True
Resume beginning
End Function

Thanks,
Thedude

Have you considered just copying sheet 6 after naming it template
sheets("Template").copy after sheets("Template")


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

wrote in message
...
I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. There is an error
check that is supposed to flag the error that occurs if a "Working
Template" sheet already exists - then delete the current (newly added)
sheet and the "Working Template", then go back to the beginning. For
some reason, though, it's just not working at all. I wrote the code
on another computer and had no problems, but on this machine it
refuses to catch it. I could rewrite it to circumvent the problem,
but it is happening elsewhere too, so i would like to get a better
understanding of why.

Any advice or recommendations would be greatly appreciated.

Function ()
beginning:
Sheets.Add After:=Sheets(6)
On Error GoTo error_occured
Sheets(7).Name = "Working Template"
Sheets(6).Cells.Copy
Sheets(7).Select
With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Cells.UnMerge
Exit Function

error_occured:
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Working Template").Delete
Application.DisplayAlerts = True
Resume beginning
End Function

Thanks,
Thedude

I'd just loop through all the sheets first to see if it exists:

Function myfunction()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In ThisWorkbook.Worksheets
If sh.Name = "Working Template" Then
sh.Delete
Exit For
End If
Next sh

' do stuff

Application.DisplayAlerts = True
End Function

--
Dan

On Jan 3, 12:00*pm, wrote:
I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. *There is an error
check that is supposed to flag the error that occurs if a "Working
Template" sheet already exists - then delete the current (newly added)
sheet and the "Working Template", then go back to the beginning. *For
some reason, though, it's just not working at all. *I wrote the code
on another computer and had no problems, but on this machine it
refuses to catch it. *I could rewrite it to circumvent the problem,
but it is happening elsewhere too, so i would like to get a better
understanding of why.

Any advice or recommendations would be greatly appreciated.

Function ()
beginning:
* * Sheets.Add After:=Sheets(6)
* * On Error GoTo error_occured
* * Sheets(7).Name = "Working Template"
* * Sheets(6).Cells.Copy
* * Sheets(7).Select
* * With Selection
* * * * .PasteSpecial Paste:=xlPasteValues
* * * * .PasteSpecial Paste:=xlPasteFormats
* * End With
* * Cells.UnMerge
Exit Function

error_occured:
* * * * Application.DisplayAlerts = False
* * * * * * ActiveSheet.Delete
* * * * * * Sheets("Working Template").Delete
* * * * Application.DisplayAlerts = True
* * * * Resume beginning
End Function

Thanks,
Thedude

Thank you everyone for your quick replies. To answer your questions:

Jim: Haha, the function is named. I missed it when i was copying
over and instead of typing out Function
create_working_template_sheet(), i took the lazy man's road and just
wrote function(). It came back to bite me in the end, but hindsight
is always 20/20 :).

Dan: That's an excellent suggestion and one i will more than likely
implement. Thank you. However, i'm still curious as to why the On
Error statement isn't catching the error. This is the first time i've
attempted to use error checking and not very familiar with how it
works. It seemed fairly straightforward, but maybe not?

Don: I'm not sure how that is different from what i've done - except
the order of the process. At the end of the macro, the "Working
Template" will be deleted, and this subroutine is set up to delete the
"Working Template" that could remain if the macro was interrupted for
some reason. It seems as though multiple Template(n) sheets could be
left, so in this situation, i don't think that would work.

I don't know why the error isn't getting flagged, but it occurs to me
that there might be a better way tpo spot your problem.
Each of the following three pages provides a different way to check if a
Sheet Exists:
http://tinyurl.com/2fm4tl
http://www.dailydoseofexcel.com/arch...8/sheetexists/
http://j-walk.com/ss/Excel/tips/tip54.htm

So, you could add one of those functions to a code module, and change
your code to to something like (untested):

Function WhateverItsCalled()
If SheetExists("Working Template") then
Application.DisplayAlerts = False
Sheets("Working Template").Delete
Application.DisplayAlerts = True
end if
Sheets.Add After:=Sheets(6)
Sheets(7).Name = "Working Template"
Sheets(6).Cells.Copy
Sheets(7).Select
With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Cells.UnMerge
end function
wrote:
I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. There is an error
check that is supposed to flag the error that occurs if a "Working
Template" sheet already exists - then delete the current (newly added)
sheet and the "Working Template", then go back to the beginning. For
some reason, though, it's just not working at all. I wrote the code
on another computer and had no problems, but on this machine it
refuses to catch it. I could rewrite it to circumvent the problem,
but it is happening elsewhere too, so i would like to get a better
understanding of why.

Any advice or recommendations would be greatly appreciated.

Function ()
beginning:
Sheets.Add After:=Sheets(6)
On Error GoTo error_occured
Sheets(7).Name = "Working Template"
Sheets(6).Cells.Copy
Sheets(7).Select
With Selection
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Cells.UnMerge
Exit Function

error_occured:
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Working Template").Delete
Application.DisplayAlerts = True
Resume beginning
End Function

Thanks,
Thedude

Thanks for the suggestions, darren. It seems like i find a new
function (SheetExists) everyday... Going back to the original
problem, here's another situation where the error isn't catching.
This function is allowing the user to select the master file. If the
user accidentally clicks a file that is already open, the message
saying its open and asking if the user wants to replace it pops up.
If 'NO' is selected, the error occurs. It SHOULD be flagged and just
restart the routine, but, like before.... nothing.

Private Sub cmb_OpenProjectFile_Click()
Dim pfilename As Variant

On Error Resume Next
beginning:

original_workbook = ActiveWorkbook.Name
pfilename = Application.GetOpenFilename _
("Excel File (*.xls),*.xls", , "Select the file to merge")


If pfilename < False Then
If pfilename < ActiveWorkbook.Name Then
On Error Resume Next
Application.Workbooks.Open (pfilename)
If ActiveWorkbook.Name = original_workbook Then _
GoTo beginning
usf_MergeData.cbo_selectProjectFile.Text =
ActiveWorkbook.Name
usf_MergeData.cbo_selectProjectFile.AddItem
ActiveWorkbook.Name

usf_MergeData.cbo_selectProjectFile.BoundColumn = 0
End If
End If

End Sub

Well, i've got a workaround done, but if anybody has any clues as to
why the On Error functions aren't working, feel free to chime in here.

Private Sub cmb_OpenProjectFile_Click()
Dim pfilename As Variant

On Error Resume Next
beginning:

original_workbook = ActiveWorkbook.Name
pfilename = Application.GetOpenFilename("Excel File (*.xls),*.xls", ,
"Select the file to merge")

slashloc = InStr(1, pfilename, "\", vbTextCompare)
Do Until slashloc = 0
slash_loc = slashloc
slashloc = InStr(slashloc + 1, pfilename, "\", vbTextCompare)
Loop
Filename = Right(pfilename, Len(pfilename) - slash_loc)

If pfilename < False Then
If Filename < ActiveWorkbook.Name Then
On Error GoTo beginning
Application.Workbooks.Open (pfilename)
If ActiveWorkbook.Name = original_workbook Then _
GoTo beginning
usf_MergeData.cbo_selectProjectFile.Text =
ActiveWorkbook.Name
usf_MergeData.cbo_selectProjectFile.AddItem
ActiveWorkbook.Name

usf_MergeData.cbo_selectProjectFile.BoundColumn = 0
End If
End If

End Sub

Regards,
Thedude

UYou have an On Error Resume Next at the start of your code. Should that
be there? It tells excel to ignore errors.

wrote:
Well, i've got a workaround done, but if anybody has any clues as to
why the On Error functions aren't working, feel free to chime in here.

Private Sub cmb_OpenProjectFile_Click()
Dim pfilename As Variant

On Error Resume Next
beginning:

original_workbook = ActiveWorkbook.Name
pfilename = Application.GetOpenFilename("Excel File (*.xls),*.xls", ,
"Select the file to merge")

slashloc = InStr(1, pfilename, "\", vbTextCompare)
Do Until slashloc = 0
slash_loc = slashloc
slashloc = InStr(slashloc + 1, pfilename, "\", vbTextCompare)
Loop
Filename = Right(pfilename, Len(pfilename) - slash_loc)

If pfilename < False Then
If Filename < ActiveWorkbook.Name Then
On Error GoTo beginning
Application.Workbooks.Open (pfilename)
If ActiveWorkbook.Name = original_workbook Then _
GoTo beginning
usf_MergeData.cbo_selectProjectFile.Text =
ActiveWorkbook.Name
usf_MergeData.cbo_selectProjectFile.AddItem
ActiveWorkbook.Name

usf_MergeData.cbo_selectProjectFile.BoundColumn = 0
End If
End If

End Sub

Regards,
Thedude

On Jan 3, 11:31 pm, Darren Hill wrote:
UYou have an On Error Resume Next at the start of your code. Should that
be there? It tells excel to ignore errors.

Darren,

I tried different variations of the On Error - the other being On
Error GoTo Beginning. My thinking with the Resume Next was that if
the file is already open, it would be the ActiveWorkbook. The error
would be skipped and the If statement following the On Error would
catch it and send it back to the beginning (because the On Error GoTo
Beginning wasn't working). On second thought, though, it seems that
checking it against all open workbooks would be the most robust
method. That link you provided should work nicely for that.

As a side note, after playing with it a bit more i'm still at a loss
as to why the error checking isn't working. Again - any ideas would
be welcomed.... anybody?

Regards,
Thedude

This has to be a settings issue. Even the Workbookisopen() function
from the link you provided is erroring out. I checked it on another
machine and it works fine...


And i'm an idiot. For the next guy that has this issue, go to Tools
=Options = General. Make sure that "Break on Every Error" is not
selected in the "Error Trapping" portion.

Thanks for your help everyone.

Regards,
Thedude