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