How do I prevent an error from crashing my macro?

[Don M.]

I use this portion of my macro to import two sheets from a workbook on our
network. The problem is that sheet B 2 may or may not be there. If it is't
then the macro just needs to go on without it and just import sheet B 1. I
need to figure out how to stop the macro from stopping and giving me an error
when sheet B 2 is not there.


' Import this weeks Work Order & Wrap Work Order
ChDir "\\fileserver\data\Global\Programs\PublicationOrdering\" & yy & mm
& sat
Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrdering\"
& yy & mm & sat & "\P___" & mm & fri & yy & ".XLS")
Sheets(Array("B 1", "B 2")).Select
Sheets("B 1").Activate
Cells.Select
Selection.Copy
Windows("Processor Run Sheet.xls").Activate
Sheets("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False


Don

 

[Rick Rothstein \(MVP - VB\)]

A structure like this should work...

For Each ws In Worksheets
If ws.Name = "B2" Then
'
' <<< Put your Sheet "B2" statements here >>>
'
Exit For
End If
Next

Rick

 

[Ron de Bruin]

One way is to use a function to check if the sheet exist

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function


In your macro you can use this then

If SheetExists("sheetname") = False Then

 

[paul.robinson]

Hi

'Tests to see if a worksheet with the given name exists in the active
workbook
Function IsSheetThere(shName As String) As Boolean
Dim DummyWks As String
IsSheetThere = False
On Error Resume Next
DummyWks = ActiveWorkbook.Worksheets(shName).Name
If Err.Number = 0 Then IsSheetThere = True
End Function


If IsSheetThere("B 2") then
Sheets(Array("B 1", "B 2")).Select
etc
Else
'something else
End If

regards
Paul

 

[BigJimmer]

Someone else may have a better way, but I would add something like the
following...

dim sh as Worksheet

On Error Resume Next
set sh = Worksheets("B 2")
On Error Resume 0 'reset error handling
if not IsEmpty(sh) then ' sh will be empty if "B 2" does not exist
Sheets(Array("B 1", "B 2")).Select
Sheets("B 1").Activate
Cells.Select
Selection.Copy
Windows("Processor Run Sheet.xls").Activate
Sheets("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End If