Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel crashing not finding error with self repair | Excel Discussion (Misc queries) | |||
How to prevent error message during macro | Excel Programming | |||
Crashing Macro, Not Enough Memory | Excel Programming | |||
Macro crashing on different computer | Excel Programming | |||
Macro Crashing Excel | Excel Programming |