Solved: Continue Macro after Error Handler

[Nicolaf]

Hi,

I have a macro which looks for a Cell in a specific sheet of a file (File Fruit) then copies it onto another file (File Data) and then looks for a Cell in a specific sheet of another file (File Veg) and again copies it onto file Data.

I have put an error Handler in case the sheet does not exist so I get a message telling me "Sorry missing sheet".

If the error occurs the second time round then all OK if error occurs on first search then I get message "Sorry missing sheet" and macro stops.

What I would like to do is to receive the message "Sorry missing sheet" but then to continue the macro the point it stopped (so would like to do second search).

Code below:



Private Sub CommandButton1_Click()

On Error GoTo ErrorHandler
ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B7").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A7").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close

ActiveSheet.Calculate
IName = ThisWorkbook.Sheets("List").Range("B8").Value 'name with extension
Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
SName = ThisWorkbook.Sheets("List").Range("A8").Value 'sheet name
NewWkbk.Sheets(SName).Select
Windows(IName).Activate
ActiveSheet.Range("M6").Select
Selection.Copy
Windows("Data.xls").Activate
Sheets("List").Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(IName).Activate
Application.CutCopyMode = False
ActiveWindow.Close

Exit Sub

ErrorHandler:
MsgBox ("Sorry missing sheet")
End Sub






How can I change my macro to do that?

:dunno :dunno

Nix

[patel]

you can check existing sheet with this function

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function
this is a sample code
Sub test()
Dim stSheetName As String
stSht = Range("C2")
If Not WorksheetExists(stSht) Then
'Sheet does not exist
'do something
Else
'Sheet exists
End If
End Sub

[Kenneth Hobs]

Prevent the error is my recommendation.

Function Test_WorkSheetExists()
MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), _
vbInformation, "ActiveWorkbook.ActiveSheet"
End Function

'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function

[Nicolaf]

thanks!

:hi: :hi: :hi:
Nix

[snb]

I prefer:


Sub tst()
MsgBox Evaluate("isref(sheet1a!A1)")
End Sub


If you are 'addicted' to functions:


Sub M_snb()
MsgBox shexist("sheet1b")
End Sub

Function shexist(c00)
shexist = Evaluate("isref(" & c00 & "!A1)")
End Function