How do I prevent an error from crashing my macro?
 

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\PublicationOrde ring\" & yy & mm
& sat
Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrd ering\"
& 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

How do I prevent an error from crashing my macro?
 

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


"Don M." wrote in message
...
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\PublicationOrde ring\" & yy & mm
& sat
Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrd ering\"
& 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

How do I prevent an error from crashing my macro?
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Don M." wrote in message ...
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\PublicationOrde ring\" & yy & mm
& sat
Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrd ering\"
& 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

How do I prevent an error from crashing my macro?
 

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



On Mar 11, 4:26*pm, Don M. wrote:
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\PublicationOrde ring\" & yy & mm
& sat
* *Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrd ering\"
& 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

How do I prevent an error from crashing my macro?
 

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


"Don M." wrote:

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\PublicationOrde ring\" & yy & mm
& sat
Workbooks.Open ("\\FileServer\Data\Global\Programs\PublicationOrd ering\"
& 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