Excel 97
I am attempting to move several sheets from one workbook to another for
purposes of emailing only the selected sheets. I am using an array to
select the sheets by name. The code works fine except when a sheet does
not exist. I would like to pop up an error message with the name of the
missing sheet and the option to continue with the sheets that exist. I have
limited experience in this area, and have tried on error goto ErrHandler,
but this does not continue moving the rest of the sheets in the array.
Below I have listed the code. Any help would be greatly appreciated.Thanks
Valerie

Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets to the workbook used for
emailing
'Opens Email to send to District manager

Workbooks.Open FileName:="\\xxx\xxx\xxx\Payroll\South Cover Sheet.xls"
Windows("cover.xls").Activate

Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Move
Befo=Workbooks( _
"South Cover Sheet.xls").Sheets(1)
ActiveWorkbook.Save
Call Email
Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"

End Sub

I have solved the one problem by not using an array (seems inefficient, but
works). My question now is how can I get the name of the missing sheet into
the error handler message? Thanks
Valerie
Revised code
Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets to the workbooks
'Opens Email to send to District manager

Workbooks.Open FileName:="\\SBSS\HOME\Valerie\Payroll\South Cover
Sheet.xls"
Windows("cover.xls").Activate

On Error GoTo NoSheet
Sheets("41 Clark.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("43 Mad.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("44 N.A.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("49 Jeff.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)


ActiveWorkbook.Save
Call Email
Application.DisplayAlerts = False
Sheets("49 Jeff.").Delete
Sheets("44 N.A.").Delete
Sheets("43 Mad.").Delete
Sheets("41 Clark.").Delete



ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"
Application.DisplayAlerts = True
Exit Sub
NoSheet:
Msg = "The sheet was not available to move or delete, would you like to
continue?"

Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then Resume Next
If Ans = vbNo Then
Windows("South Cover Sheet.xls").Activate
ActiveWindow.Close
End If

End Sub

"V. Roe" wrote in message
...
Excel 97
I am attempting to move several sheets from one workbook to another for
purposes of emailing only the selected sheets. I am using an array to
select the sheets by name. The code works fine except when a sheet does
not exist. I would like to pop up an error message with the name of the
missing sheet and the option to continue with the sheets that exist. I
have
limited experience in this area, and have tried on error goto ErrHandler,
but this does not continue moving the rest of the sheets in the array.
Below I have listed the code. Any help would be greatly appreciated.Thanks
Valerie

Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets to the workbook used for
emailing
'Opens Email to send to District manager

Workbooks.Open FileName:="\\xxx\xxx\xxx\Payroll\South Cover Sheet.xls"
Windows("cover.xls").Activate

Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Move
Befo=Workbooks( _
"South Cover Sheet.xls").Sheets(1)
ActiveWorkbook.Save
Call Email
Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"

End Sub

Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets
' to the workbook used for
' emailing
'Opens Email to send to District manager
Dim ans As Variant
Dim varr As Variant
Dim sStr As String
Dim i As Long, j As Long
Dim sh As Worksheet, shts As Sheets
Dim bFirst As Boolean
Dim varr1() As String

Workbooks.Open Filename:="C:\Data5\South Cover Sheet.xls"
Windows("cover.xls").Activate
varr = Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")
bFirst = True
sStr = ""
ReDim varr1(LBound(varr) To UBound(varr))
j = LBound(varr)
For i = LBound(varr) To UBound(varr)
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(varr(i))
On Error GoTo 0
If Not sh Is Nothing Then
sh.Select bFirst
bFirst = False
varr1(j) = sh.Name
j = j + 1
Else
sStr = sStr & varr(i) & vbNewLine
End If
Next
If j = LBound(varr) Then
If varr1(j) = "" Then
MsgBox "No sheets found"
Workbooks("South Cover Sheet.xls").Close _
SaveChanges:=False
Exit Sub
End If
End If
If sStr < "" Then
sStr = "Following sheets are missing:" & vbNewLine & sStr
sStr = sStr & vbNewLine & vbNewLine & "continue?"
ans = MsgBox(sStr, _
vbYesNo + vbCritical + vbQuestion, "Missing Sheets:")
If ans = vbNo Then
' perform cleanup
Workbooks("South Cover Sheet.xls").Close _
SaveChanges:=False
Exit Sub
End If
End If
ActiveWindow.SelectedSheets.Move _
Befo=Workbooks( _
"South Cover Sheet.xls").Sheets(1)
ActiveWorkbook.Save
Call Email
Application.DisplayAlerts = False
Sheets(varr1).Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"

End Sub

--
Regards,
Tom Ogilvy


"V. Roe" wrote in message
...
I have solved the one problem by not using an array (seems inefficient,
but
works). My question now is how can I get the name of the missing sheet
into
the error handler message? Thanks
Valerie
Revised code
Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets to the workbooks
'Opens Email to send to District manager

Workbooks.Open FileName:="\\SBSS\HOME\Valerie\Payroll\South Cover
Sheet.xls"
Windows("cover.xls").Activate

On Error GoTo NoSheet
Sheets("41 Clark.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("43 Mad.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("44 N.A.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)
Windows("cover.xls").Activate
Sheets("49 Jeff.").Move Befo=Workbooks("South Cover
Sheet.xls").Sheets(1)


ActiveWorkbook.Save
Call Email
Application.DisplayAlerts = False
Sheets("49 Jeff.").Delete
Sheets("44 N.A.").Delete
Sheets("43 Mad.").Delete
Sheets("41 Clark.").Delete



ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"
Application.DisplayAlerts = True
Exit Sub
NoSheet:
Msg = "The sheet was not available to move or delete, would you like to
continue?"

Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then Resume Next
If Ans = vbNo Then
Windows("South Cover Sheet.xls").Activate
ActiveWindow.Close
End If

End Sub

"V. Roe" wrote in message
...
Excel 97
I am attempting to move several sheets from one workbook to another for
purposes of emailing only the selected sheets. I am using an array to
select the sheets by name. The code works fine except when a sheet
does
not exist. I would like to pop up an error message with the name of the
missing sheet and the option to continue with the sheets that exist. I
have
limited experience in this area, and have tried on error goto
ErrHandler,
but this does not continue moving the rest of the sheets in the array.
Below I have listed the code. Any help would be greatly
appreciated.Thanks
Valerie

Sub ExportSouth()
'Macro Step 3 Export South Sheets
'Opens South Workbook and moves store Sheets to the workbook used for
emailing
'Opens Email to send to District manager

Workbooks.Open FileName:="\\xxx\xxx\xxx\Payroll\South Cover
Sheet.xls"
Windows("cover.xls").Activate

Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Move
Befo=Workbooks( _
"South Cover Sheet.xls").Sheets(1)
ActiveWorkbook.Save
Call Email
Sheets(Array("41 Clark.", "43 Mad.", "44 N.A.", "49 Jeff.")).Delete
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("cover.xls").Activate
Sheets("check List").Range("b9") = "X"

End Sub