Hi -

I've searched the archives for help on this, but have succeeded in
only confusing myself further -- so I need your help!

I need to check that a workbook contains all 3 worksheets in a
specified array, creating any that are missing -- the code will
(eventually) transfer data from 'basebook' to 'HoursFile'. My
current code is below -- why isn't it working? The line it errors on
is marked.

Thanks, ray


Private Sub SubmitWeekly_Click()
Dim basebook As Workbook, Path As String, HoursFile As Workbook
Dim sh As Worksheet

Set basebook = ThisWorkbook
Store = basebook.Sheets("DB - Hours Reporting").Range("K7").Value
Path = "\\.....\FY08\Hours Report_" & Store & ".xls"

' Check that a STORE # has been entered
If Store = "" Then
Call MsgBox("You MUST select a store before continuing
with this action ....", vbExclamation, "No store selected!")
basebook.Sheets("DB - Hours
Reporting").Range("K7").Select
Exit Sub
Else
End If

If Dir(Path) = "" Then
Workbooks.Add.SaveAs Filename:=Path
Sheets.Add.Name = "wkly Sales"
Sheets.Add.Name = "wkly Hours"
Sheets.Add.Name = "mthly hours"
Else
End If

Set HoursFile = Workbooks.Open(Filename:=Path)

sh = Sheets(Array("wkly Sales", "wkly Hours", "mthly hours"))
'ERRORS OUT HERE!
For Each Sheet In sh
If Not WorksheetExists(sh, HoursFile) Then Sheets.Add.Name
= sh
Next

ActiveSheet.Range("G20").Value = Now

End Sub