Hi,

How do I capture a Run-time error '1004': Copy Method of Worksheet Class
failed or the like so I retain control of the code?

--
Thanks for your help.
Karen53

On error resume next 'you're expecting that the next line MIGHT cause an error
'your code
if err.number <> 0 then
msgbox err.description & vblf & err.number
err.clear
else
'no error occurred
end if
on error goto 0 'let excel handle the errors




Karen53 wrote:
>
> Hi,
>
> How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> failed or the like so I retain control of the code?
>
> --
> Thanks for your help.
> Karen53

--

Dave Peterson

You could put an error trap in your code or you can totally ignore your
errors like this:

On Error Resume Next

If you want an example of an error trap, take a look at the following
example:

Sub test()

On Error GoTo test_Error

' your code goes here

On Error GoTo 0
Exit Sub

test_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
test of Module Module2"
End Sub

Mark

Hi Dave,

This is what I'm trying to do using part of what you posted. Will this
work? I'm trying to capture when the worksheet copy errors out and prompt
the user what to do.

Sub GetTenantInfo()

Dim wbkCopyFrom As Workbook
Dim wbkCopyTo As Workbook
Dim FromwbkPath
Dim ShName As String
Dim CopyWSError As Boolean

CopyWSError = False
Set wbkCopyTo = ThisWorkbook

FromwbkPath = Application.GetOpenFilename

On Error Resume Next

Set wbkCopyFrom = Workbooks(FromwbkPath)
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Application.StatusBar = "Processing. Please Wait."
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

My Code

'copy the sheet
Call AddSheets.UnProtectWkbook
CAMMaster.Copy After:=Sheets(ShNumber)

If Err.Number <> 0 Then
Err.Clear
CopyWSError = True
GoTo Finished
Else
'no error occurred
End If

'name the sheet
ActiveSheet.Name = (ShName)
Call ProtectSht(ShName)
Call AddSheets.ProtectWkbook

More Code

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False

wbkCopyTo.Save

End If
End If

Finished:

If CopyWSError = True Then
wbkCopyTo.Save
MsgBox "Maximum tenant sheets copied." & vbLf _
& "Close workbook, reopen and restart 'Recreate Tenant
Sheets'"
End If

End Sub

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

> On error resume next 'you're expecting that the next line MIGHT cause an error
> 'your code
> if err.number <> 0 then
> msgbox err.description & vblf & err.number
> err.clear
> else
> 'no error occurred
> end if
> on error goto 0 'let excel handle the errors
>
>
>
>
> Karen53 wrote:
> >
> > Hi,
> >
> > How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> > failed or the like so I retain control of the code?
> >
> > --
> > Thanks for your help.
> > Karen53
>
> --
>
> Dave Peterson
>

Two quick comments...

First, you should put these lines...

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False

in the Finished error handler, otherwise they won't get turned back in if an
error occurs.

Second, you can't check the Err.Number after you execute On Error GoTo 0 as
it will be 0.

Rick


"Karen53" <(E-Mail Removed)> wrote in message
news:2D29DF4F-FBA0-4666-8714-(E-Mail Removed)...
> Hi Dave,
>
> This is what I'm trying to do using part of what you posted. Will this
> work? I'm trying to capture when the worksheet copy errors out and prompt
> the user what to do.
>
> Sub GetTenantInfo()
>
> Dim wbkCopyFrom As Workbook
> Dim wbkCopyTo As Workbook
> Dim FromwbkPath
> Dim ShName As String
> Dim CopyWSError As Boolean
>
> CopyWSError = False
> Set wbkCopyTo = ThisWorkbook
>
> FromwbkPath = Application.GetOpenFilename
>
> On Error Resume Next
>
> Set wbkCopyFrom = Workbooks(FromwbkPath)
> If wbkCopyFrom Is Nothing Then
> Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> On Error GoTo 0
> If wbkCopyFrom Is Nothing Then
> MsgBox "Cannot find originating file"
> Else
>
> Application.StatusBar = "Processing. Please Wait."
> Application.ScreenUpdating = False
> Application.EnableEvents = False
> Application.Calculation = xlCalculationManual
>
> My Code
>
> 'copy the sheet
> Call AddSheets.UnProtectWkbook
> CAMMaster.Copy After:=Sheets(ShNumber)
>
> If Err.Number <> 0 Then
> Err.Clear
> CopyWSError = True
> GoTo Finished
> Else
> 'no error occurred
> End If
>
> 'name the sheet
> ActiveSheet.Name = (ShName)
> Call ProtectSht(ShName)
> Call AddSheets.ProtectWkbook
>
> More Code
>
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> Application.StatusBar = False
>
> wbkCopyTo.Save
>
> End If
> End If
>
> Finished:
>
> If CopyWSError = True Then
> wbkCopyTo.Save
> MsgBox "Maximum tenant sheets copied." & vbLf _
> & "Close workbook, reopen and restart 'Recreate Tenant
> Sheets'"
> End If
>
> End Sub
>
> --
> Thanks for your help.
> Karen53
>
>
> "Dave Peterson" wrote:
>
>> On error resume next 'you're expecting that the next line MIGHT cause an
>> error
>> 'your code
>> if err.number <> 0 then
>> msgbox err.description & vblf & err.number
>> err.clear
>> else
>> 'no error occurred
>> end if
>> on error goto 0 'let excel handle the errors
>>
>>
>>
>>
>> Karen53 wrote:
>> >
>> > Hi,
>> >
>> > How do I capture a Run-time error '1004': Copy Method of Worksheet
>> > Class
>> > failed or the like so I retain control of the code?
>> >
>> > --
>> > Thanks for your help.
>> > Karen53
>>
>> --
>>
>> Dave Peterson
>>

There's a lot of "more code", but this untested, uncompiled code may be closer:

Option Explicit
Sub GetTenantInfo()

Dim wbkCopyFrom As Workbook
Dim wbkCopyFromName As String
Dim wbkCopyTo As Workbook
Dim FromwbkPath As Variant 'I like to see "As Variant"
Dim ShName As String
Dim CopyWSError As Boolean

CopyWSError = False
Set wbkCopyTo = ThisWorkbook

FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls")
If FromwbkPath = False Then
Exit Sub 'user hit cancel
End If

'just the filename
wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1)

On Error Resume Next
'you don't use the whole path--just the filename here
Set wbkCopyFrom = Workbooks(wbkCopyFromName)
On Error GoTo 0

If wbkCopyFrom Is Nothing Then
On Error Resume Next
Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath)
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot Open originating file--in use or wrong password?"
Exit Sub
Else
Application.StatusBar = "Processing. Please Wait."
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'My Code

'copy the sheet
Call AddSheets.UnProtectWkbook

On Error Resume Next
CAMMaster.Copy After:=Sheets(ShNumber)
If Err.Number <> 0 Then
Err.Clear
CopyWSError = True
GoTo Finished
Else
'no error occurred
End If
On Error goto 0

'name the sheet
ActiveSheet.Name = ShName
Call ProtectSht(ShName)
Call AddSheets.ProtectWkbook

More Code

wbkCopyTo.Save

End If
End If

Finished:

If CopyWSError = True Then
wbkCopyTo.Save 'save it even if there was an error?????
MsgBox "Maximum tenant sheets copied." & vbLf _
& "Close workbook, reopen and restart 'Recreate Tenant Sheets'"
End If

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub


Karen53 wrote:
>
> Hi Dave,
>
> This is what I'm trying to do using part of what you posted. Will this
> work? I'm trying to capture when the worksheet copy errors out and prompt
> the user what to do.
>
> Sub GetTenantInfo()
>
> Dim wbkCopyFrom As Workbook
> Dim wbkCopyTo As Workbook
> Dim FromwbkPath
> Dim ShName As String
> Dim CopyWSError As Boolean
>
> CopyWSError = False
> Set wbkCopyTo = ThisWorkbook
>
> FromwbkPath = Application.GetOpenFilename
>
> On Error Resume Next
>
> Set wbkCopyFrom = Workbooks(FromwbkPath)
> If wbkCopyFrom Is Nothing Then
> Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> On Error GoTo 0
> If wbkCopyFrom Is Nothing Then
> MsgBox "Cannot find originating file"
> Else
>
> Application.StatusBar = "Processing. Please Wait."
> Application.ScreenUpdating = False
> Application.EnableEvents = False
> Application.Calculation = xlCalculationManual
>
> My Code
>
> 'copy the sheet
> Call AddSheets.UnProtectWkbook
> CAMMaster.Copy After:=Sheets(ShNumber)
>
> If Err.Number <> 0 Then
> Err.Clear
> CopyWSError = True
> GoTo Finished
> Else
> 'no error occurred
> End If
>
> 'name the sheet
> ActiveSheet.Name = (ShName)
> Call ProtectSht(ShName)
> Call AddSheets.ProtectWkbook
>
> More Code
>
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> Application.StatusBar = False
>
> wbkCopyTo.Save
>
> End If
> End If
>
> Finished:
>
> If CopyWSError = True Then
> wbkCopyTo.Save
> MsgBox "Maximum tenant sheets copied." & vbLf _
> & "Close workbook, reopen and restart 'Recreate Tenant
> Sheets'"
> End If
>
> End Sub
>
> --
> Thanks for your help.
> Karen53
>
> "Dave Peterson" wrote:
>
> > On error resume next 'you're expecting that the next line MIGHT cause an error
> > 'your code
> > if err.number <> 0 then
> > msgbox err.description & vblf & err.number
> > err.clear
> > else
> > 'no error occurred
> > end if
> > on error goto 0 'let excel handle the errors
> >
> >
> >
> >
> > Karen53 wrote:
> > >
> > > Hi,
> > >
> > > How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> > > failed or the like so I retain control of the code?
> > >
> > > --
> > > Thanks for your help.
> > > Karen53
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi,

If I move it to a called procedure, would it work?

'copy the sheet
Call wsCopy(ShNumber, CopyWSError)

If CopyWSError = True Then
wkbcopyfrom.Close SaveAs:=False

Call AddSheets.UnProtectSht(Replace(MainPagepg.Name, "'",
"''"))

'delete the errored row
MainPagepg.Rows(iCtr).Delete (xlUp)

Call AddSheets.ProtectSht(Replace(MainPagepg.Name, "'", "''"))

'reset Tenant's range
MainPagepg.Range("C" & 14 & ":F" & iCtr - 1).Name = "Tenants"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False

wbkCopyTo.Save
MsgBox "Maximum sheets copied." & vbLf _
& "Close workbook, reopen and" & vbLf & _
restart 'Recreate Tenant Sheets'"

GoTo Finished
Else
'no error occurred
End If

Finished:
End Sub


Sub wsCopy()

'copy the sheet

Call AddSheets.UnProtectWkbook

On Error Resume Next
CAMMaster.Copy After:=Sheets(ShNumber)
Call wsCopy(ShNumber, CopyWSError)

If Err.Number <> 0 Then
Err.Clear
CopyWSError = True
Else
'no error occurred
End If

End Sub


--
Thanks for your help.
Karen53


"Rick Rothstein (MVP - VB)" wrote:

> Two quick comments...
>
> First, you should put these lines...
>
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> Application.StatusBar = False
>
> in the Finished error handler, otherwise they won't get turned back in if an
> error occurs.
>
> Second, you can't check the Err.Number after you execute On Error GoTo 0 as
> it will be 0.
>
> Rick
>
>
> "Karen53" <(E-Mail Removed)> wrote in message
> news:2D29DF4F-FBA0-4666-8714-(E-Mail Removed)...
> > Hi Dave,
> >
> > This is what I'm trying to do using part of what you posted. Will this
> > work? I'm trying to capture when the worksheet copy errors out and prompt
> > the user what to do.
> >
> > Sub GetTenantInfo()
> >
> > Dim wbkCopyFrom As Workbook
> > Dim wbkCopyTo As Workbook
> > Dim FromwbkPath
> > Dim ShName As String
> > Dim CopyWSError As Boolean
> >
> > CopyWSError = False
> > Set wbkCopyTo = ThisWorkbook
> >
> > FromwbkPath = Application.GetOpenFilename
> >
> > On Error Resume Next
> >
> > Set wbkCopyFrom = Workbooks(FromwbkPath)
> > If wbkCopyFrom Is Nothing Then
> > Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> > On Error GoTo 0
> > If wbkCopyFrom Is Nothing Then
> > MsgBox "Cannot find originating file"
> > Else
> >
> > Application.StatusBar = "Processing. Please Wait."
> > Application.ScreenUpdating = False
> > Application.EnableEvents = False
> > Application.Calculation = xlCalculationManual
> >
> > My Code
> >
> > 'copy the sheet
> > Call AddSheets.UnProtectWkbook
> > CAMMaster.Copy After:=Sheets(ShNumber)
> >
> > If Err.Number <> 0 Then
> > Err.Clear
> > CopyWSError = True
> > GoTo Finished
> > Else
> > 'no error occurred
> > End If
> >
> > 'name the sheet
> > ActiveSheet.Name = (ShName)
> > Call ProtectSht(ShName)
> > Call AddSheets.ProtectWkbook
> >
> > More Code
> >
> > Application.Calculation = xlCalculationAutomatic
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> > Application.StatusBar = False
> >
> > wbkCopyTo.Save
> >
> > End If
> > End If
> >
> > Finished:
> >
> > If CopyWSError = True Then
> > wbkCopyTo.Save
> > MsgBox "Maximum tenant sheets copied." & vbLf _
> > & "Close workbook, reopen and restart 'Recreate Tenant
> > Sheets'"
> > End If
> >
> > End Sub
> >
> > --
> > Thanks for your help.
> > Karen53
> >
> >
> > "Dave Peterson" wrote:
> >
> >> On error resume next 'you're expecting that the next line MIGHT cause an
> >> error
> >> 'your code
> >> if err.number <> 0 then
> >> msgbox err.description & vblf & err.number
> >> err.clear
> >> else
> >> 'no error occurred
> >> end if
> >> on error goto 0 'let excel handle the errors
> >>
> >>
> >>
> >>
> >> Karen53 wrote:
> >> >
> >> > Hi,
> >> >
> >> > How do I capture a Run-time error '1004': Copy Method of Worksheet
> >> > Class
> >> > failed or the like so I retain control of the code?
> >> >
> >> > --
> >> > Thanks for your help.
> >> > Karen53
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>

Hi Dave,

Thanks! I will be able to test it tomorrow at work.

wbkCopyTo.Save 'save it even if there was an error?????

The only error I want to catch is the worksheet copy error. The MasterCAM
sheet is copied repeatedly for the tenant's sheets within the same workbook.
Excel errors out after so many copies. The workbook then has to be saved,
closed and reopened to resume. So, any sheets copied before the error are
good and need to be saved. I can't use an external template because the
MasterCAM contains links within the workbook. Plus, the client doesn't want
to use an external template. So, depending on the number of tenant's sheets,
the save, close, open and rerun "Recreate Tenant's Sheets" routine may have
to run several times to get through all of the tenant's sheets. I'm trying
to catch it and automate it as much a possible.

--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

> There's a lot of "more code", but this untested, uncompiled code may be closer:
>
> Option Explicit
> Sub GetTenantInfo()
>
> Dim wbkCopyFrom As Workbook
> Dim wbkCopyFromName As String
> Dim wbkCopyTo As Workbook
> Dim FromwbkPath As Variant 'I like to see "As Variant"
> Dim ShName As String
> Dim CopyWSError As Boolean
>
> CopyWSError = False
> Set wbkCopyTo = ThisWorkbook
>
> FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls")
> If FromwbkPath = False Then
> Exit Sub 'user hit cancel
> End If
>
> 'just the filename
> wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1)
>
> On Error Resume Next
> 'you don't use the whole path--just the filename here
> Set wbkCopyFrom = Workbooks(wbkCopyFromName)
> On Error GoTo 0
>
> If wbkCopyFrom Is Nothing Then
> On Error Resume Next
> Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath)
> On Error GoTo 0
> If wbkCopyFrom Is Nothing Then
> MsgBox "Cannot Open originating file--in use or wrong password?"
> Exit Sub
> Else
> Application.StatusBar = "Processing. Please Wait."
> Application.ScreenUpdating = False
> Application.EnableEvents = False
> Application.Calculation = xlCalculationManual
>
> 'My Code
>
> 'copy the sheet
> Call AddSheets.UnProtectWkbook
>
> On Error Resume Next
> CAMMaster.Copy After:=Sheets(ShNumber)
> If Err.Number <> 0 Then
> Err.Clear
> CopyWSError = True
> GoTo Finished
> Else
> 'no error occurred
> End If
> On Error goto 0
>
> 'name the sheet
> ActiveSheet.Name = ShName
> Call ProtectSht(ShName)
> Call AddSheets.ProtectWkbook
>
> More Code
>
> wbkCopyTo.Save
>
> End If
> End If
>
> Finished:
>
> If CopyWSError = True Then
> wbkCopyTo.Save 'save it even if there was an error?????
> MsgBox "Maximum tenant sheets copied." & vbLf _
> & "Close workbook, reopen and restart 'Recreate Tenant Sheets'"
> End If
>
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> Application.StatusBar = False
>
> End Sub
>
>
> Karen53 wrote:
> >
> > Hi Dave,
> >
> > This is what I'm trying to do using part of what you posted. Will this
> > work? I'm trying to capture when the worksheet copy errors out and prompt
> > the user what to do.
> >
> > Sub GetTenantInfo()
> >
> > Dim wbkCopyFrom As Workbook
> > Dim wbkCopyTo As Workbook
> > Dim FromwbkPath
> > Dim ShName As String
> > Dim CopyWSError As Boolean
> >
> > CopyWSError = False
> > Set wbkCopyTo = ThisWorkbook
> >
> > FromwbkPath = Application.GetOpenFilename
> >
> > On Error Resume Next
> >
> > Set wbkCopyFrom = Workbooks(FromwbkPath)
> > If wbkCopyFrom Is Nothing Then
> > Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> > On Error GoTo 0
> > If wbkCopyFrom Is Nothing Then
> > MsgBox "Cannot find originating file"
> > Else
> >
> > Application.StatusBar = "Processing. Please Wait."
> > Application.ScreenUpdating = False
> > Application.EnableEvents = False
> > Application.Calculation = xlCalculationManual
> >
> > My Code
> >
> > 'copy the sheet
> > Call AddSheets.UnProtectWkbook
> > CAMMaster.Copy After:=Sheets(ShNumber)
> >
> > If Err.Number <> 0 Then
> > Err.Clear
> > CopyWSError = True
> > GoTo Finished
> > Else
> > 'no error occurred
> > End If
> >
> > 'name the sheet
> > ActiveSheet.Name = (ShName)
> > Call ProtectSht(ShName)
> > Call AddSheets.ProtectWkbook
> >
> > More Code
> >
> > Application.Calculation = xlCalculationAutomatic
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> > Application.StatusBar = False
> >
> > wbkCopyTo.Save
> >
> > End If
> > End If
> >
> > Finished:
> >
> > If CopyWSError = True Then
> > wbkCopyTo.Save
> > MsgBox "Maximum tenant sheets copied." & vbLf _
> > & "Close workbook, reopen and restart 'Recreate Tenant
> > Sheets'"
> > End If
> >
> > End Sub
> >
> > --
> > Thanks for your help.
> > Karen53
> >
> > "Dave Peterson" wrote:
> >
> > > On error resume next 'you're expecting that the next line MIGHT cause an error
> > > 'your code
> > > if err.number <> 0 then
> > > msgbox err.description & vblf & err.number
> > > err.clear
> > > else
> > > 'no error occurred
> > > end if
> > > on error goto 0 'let excel handle the errors
> > >
> > >
> > >
> > >
> > > Karen53 wrote:
> > > >
> > > > Hi,
> > > >
> > > > How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> > > > failed or the like so I retain control of the code?
> > > >
> > > > --
> > > > Thanks for your help.
> > > > Karen53
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Hi Dave,

If On Error Resume Next is used, how long does it stay in effect? Does it
stay in effect for the entire procedure and any sub procedures it calls?
What is it's scope?
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

> There's a lot of "more code", but this untested, uncompiled code may be closer:
>
> Option Explicit
> Sub GetTenantInfo()
>
> Dim wbkCopyFrom As Workbook
> Dim wbkCopyFromName As String
> Dim wbkCopyTo As Workbook
> Dim FromwbkPath As Variant 'I like to see "As Variant"
> Dim ShName As String
> Dim CopyWSError As Boolean
>
> CopyWSError = False
> Set wbkCopyTo = ThisWorkbook
>
> FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls")
> If FromwbkPath = False Then
> Exit Sub 'user hit cancel
> End If
>
> 'just the filename
> wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1)
>
> On Error Resume Next
> 'you don't use the whole path--just the filename here
> Set wbkCopyFrom = Workbooks(wbkCopyFromName)
> On Error GoTo 0
>
> If wbkCopyFrom Is Nothing Then
> On Error Resume Next
> Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath)
> On Error GoTo 0
> If wbkCopyFrom Is Nothing Then
> MsgBox "Cannot Open originating file--in use or wrong password?"
> Exit Sub
> Else
> Application.StatusBar = "Processing. Please Wait."
> Application.ScreenUpdating = False
> Application.EnableEvents = False
> Application.Calculation = xlCalculationManual
>
> 'My Code
>
> 'copy the sheet
> Call AddSheets.UnProtectWkbook
>
> On Error Resume Next
> CAMMaster.Copy After:=Sheets(ShNumber)
> If Err.Number <> 0 Then
> Err.Clear
> CopyWSError = True
> GoTo Finished
> Else
> 'no error occurred
> End If
> On Error goto 0
>
> 'name the sheet
> ActiveSheet.Name = ShName
> Call ProtectSht(ShName)
> Call AddSheets.ProtectWkbook
>
> More Code
>
> wbkCopyTo.Save
>
> End If
> End If
>
> Finished:
>
> If CopyWSError = True Then
> wbkCopyTo.Save 'save it even if there was an error?????
> MsgBox "Maximum tenant sheets copied." & vbLf _
> & "Close workbook, reopen and restart 'Recreate Tenant Sheets'"
> End If
>
> Application.Calculation = xlCalculationAutomatic
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> Application.StatusBar = False
>
> End Sub
>
>
> Karen53 wrote:
> >
> > Hi Dave,
> >
> > This is what I'm trying to do using part of what you posted. Will this
> > work? I'm trying to capture when the worksheet copy errors out and prompt
> > the user what to do.
> >
> > Sub GetTenantInfo()
> >
> > Dim wbkCopyFrom As Workbook
> > Dim wbkCopyTo As Workbook
> > Dim FromwbkPath
> > Dim ShName As String
> > Dim CopyWSError As Boolean
> >
> > CopyWSError = False
> > Set wbkCopyTo = ThisWorkbook
> >
> > FromwbkPath = Application.GetOpenFilename
> >
> > On Error Resume Next
> >
> > Set wbkCopyFrom = Workbooks(FromwbkPath)
> > If wbkCopyFrom Is Nothing Then
> > Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> > On Error GoTo 0
> > If wbkCopyFrom Is Nothing Then
> > MsgBox "Cannot find originating file"
> > Else
> >
> > Application.StatusBar = "Processing. Please Wait."
> > Application.ScreenUpdating = False
> > Application.EnableEvents = False
> > Application.Calculation = xlCalculationManual
> >
> > My Code
> >
> > 'copy the sheet
> > Call AddSheets.UnProtectWkbook
> > CAMMaster.Copy After:=Sheets(ShNumber)
> >
> > If Err.Number <> 0 Then
> > Err.Clear
> > CopyWSError = True
> > GoTo Finished
> > Else
> > 'no error occurred
> > End If
> >
> > 'name the sheet
> > ActiveSheet.Name = (ShName)
> > Call ProtectSht(ShName)
> > Call AddSheets.ProtectWkbook
> >
> > More Code
> >
> > Application.Calculation = xlCalculationAutomatic
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> > Application.StatusBar = False
> >
> > wbkCopyTo.Save
> >
> > End If
> > End If
> >
> > Finished:
> >
> > If CopyWSError = True Then
> > wbkCopyTo.Save
> > MsgBox "Maximum tenant sheets copied." & vbLf _
> > & "Close workbook, reopen and restart 'Recreate Tenant
> > Sheets'"
> > End If
> >
> > End Sub
> >
> > --
> > Thanks for your help.
> > Karen53
> >
> > "Dave Peterson" wrote:
> >
> > > On error resume next 'you're expecting that the next line MIGHT cause an error
> > > 'your code
> > > if err.number <> 0 then
> > > msgbox err.description & vblf & err.number
> > > err.clear
> > > else
> > > 'no error occurred
> > > end if
> > > on error goto 0 'let excel handle the errors
> > >
> > >
> > >
> > >
> > > Karen53 wrote:
> > > >
> > > > Hi,
> > > >
> > > > How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> > > > failed or the like so I retain control of the code?
> > > >
> > > > --
> > > > Thanks for your help.
> > > > Karen53
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Chip Pearson covers it in great detail:
http://cpearson.com/excel/ErrorHandling.htm



Karen53 wrote:
>
> Hi Dave,
>
> If On Error Resume Next is used, how long does it stay in effect? Does it
> stay in effect for the entire procedure and any sub procedures it calls?
> What is it's scope?
> --
> Thanks for your help.
> Karen53
>
> "Dave Peterson" wrote:
>
> > There's a lot of "more code", but this untested, uncompiled code may be closer:
> >
> > Option Explicit
> > Sub GetTenantInfo()
> >
> > Dim wbkCopyFrom As Workbook
> > Dim wbkCopyFromName As String
> > Dim wbkCopyTo As Workbook
> > Dim FromwbkPath As Variant 'I like to see "As Variant"
> > Dim ShName As String
> > Dim CopyWSError As Boolean
> >
> > CopyWSError = False
> > Set wbkCopyTo = ThisWorkbook
> >
> > FromwbkPath = Application.GetOpenFilename(Filefilter:="Excel Files, *.xls")
> > If FromwbkPath = False Then
> > Exit Sub 'user hit cancel
> > End If
> >
> > 'just the filename
> > wbkCopyFromName = Mid(FromwbkPath, InStrRev(FromwbkPath, "\") + 1)
> >
> > On Error Resume Next
> > 'you don't use the whole path--just the filename here
> > Set wbkCopyFrom = Workbooks(wbkCopyFromName)
> > On Error GoTo 0
> >
> > If wbkCopyFrom Is Nothing Then
> > On Error Resume Next
> > Set wbkCopyFrom = Workbooks.Open(Filename:=FromwbkPath)
> > On Error GoTo 0
> > If wbkCopyFrom Is Nothing Then
> > MsgBox "Cannot Open originating file--in use or wrong password?"
> > Exit Sub
> > Else
> > Application.StatusBar = "Processing. Please Wait."
> > Application.ScreenUpdating = False
> > Application.EnableEvents = False
> > Application.Calculation = xlCalculationManual
> >
> > 'My Code
> >
> > 'copy the sheet
> > Call AddSheets.UnProtectWkbook
> >
> > On Error Resume Next
> > CAMMaster.Copy After:=Sheets(ShNumber)
> > If Err.Number <> 0 Then
> > Err.Clear
> > CopyWSError = True
> > GoTo Finished
> > Else
> > 'no error occurred
> > End If
> > On Error goto 0
> >
> > 'name the sheet
> > ActiveSheet.Name = ShName
> > Call ProtectSht(ShName)
> > Call AddSheets.ProtectWkbook
> >
> > More Code
> >
> > wbkCopyTo.Save
> >
> > End If
> > End If
> >
> > Finished:
> >
> > If CopyWSError = True Then
> > wbkCopyTo.Save 'save it even if there was an error?????
> > MsgBox "Maximum tenant sheets copied." & vbLf _
> > & "Close workbook, reopen and restart 'Recreate Tenant Sheets'"
> > End If
> >
> > Application.Calculation = xlCalculationAutomatic
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> > Application.StatusBar = False
> >
> > End Sub
> >
> >
> > Karen53 wrote:
> > >
> > > Hi Dave,
> > >
> > > This is what I'm trying to do using part of what you posted. Will this
> > > work? I'm trying to capture when the worksheet copy errors out and prompt
> > > the user what to do.
> > >
> > > Sub GetTenantInfo()
> > >
> > > Dim wbkCopyFrom As Workbook
> > > Dim wbkCopyTo As Workbook
> > > Dim FromwbkPath
> > > Dim ShName As String
> > > Dim CopyWSError As Boolean
> > >
> > > CopyWSError = False
> > > Set wbkCopyTo = ThisWorkbook
> > >
> > > FromwbkPath = Application.GetOpenFilename
> > >
> > > On Error Resume Next
> > >
> > > Set wbkCopyFrom = Workbooks(FromwbkPath)
> > > If wbkCopyFrom Is Nothing Then
> > > Set wbkCopyFrom = Workbooks.Open(FromwbkPath)
> > > On Error GoTo 0
> > > If wbkCopyFrom Is Nothing Then
> > > MsgBox "Cannot find originating file"
> > > Else
> > >
> > > Application.StatusBar = "Processing. Please Wait."
> > > Application.ScreenUpdating = False
> > > Application.EnableEvents = False
> > > Application.Calculation = xlCalculationManual
> > >
> > > My Code
> > >
> > > 'copy the sheet
> > > Call AddSheets.UnProtectWkbook
> > > CAMMaster.Copy After:=Sheets(ShNumber)
> > >
> > > If Err.Number <> 0 Then
> > > Err.Clear
> > > CopyWSError = True
> > > GoTo Finished
> > > Else
> > > 'no error occurred
> > > End If
> > >
> > > 'name the sheet
> > > ActiveSheet.Name = (ShName)
> > > Call ProtectSht(ShName)
> > > Call AddSheets.ProtectWkbook
> > >
> > > More Code
> > >
> > > Application.Calculation = xlCalculationAutomatic
> > > Application.EnableEvents = True
> > > Application.ScreenUpdating = True
> > > Application.StatusBar = False
> > >
> > > wbkCopyTo.Save
> > >
> > > End If
> > > End If
> > >
> > > Finished:
> > >
> > > If CopyWSError = True Then
> > > wbkCopyTo.Save
> > > MsgBox "Maximum tenant sheets copied." & vbLf _
> > > & "Close workbook, reopen and restart 'Recreate Tenant
> > > Sheets'"
> > > End If
> > >
> > > End Sub
> > >
> > > --
> > > Thanks for your help.
> > > Karen53
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > On error resume next 'you're expecting that the next line MIGHT cause an error
> > > > 'your code
> > > > if err.number <> 0 then
> > > > msgbox err.description & vblf & err.number
> > > > err.clear
> > > > else
> > > > 'no error occurred
> > > > end if
> > > > on error goto 0 'let excel handle the errors
> > > >
> > > >
> > > >
> > > >
> > > > Karen53 wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > How do I capture a Run-time error '1004': Copy Method of Worksheet Class
> > > > > failed or the like so I retain control of the code?
> > > > >
> > > > > --
> > > > > Thanks for your help.
> > > > > Karen53
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson