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" wrote in message
...
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