Immediately before the .Select command put a

MSGBOX(sShtName) to make sure the sheet exists in the workbook.
--
Gary''s Student


"davegb" wrote:

The following is the beginning of some code to copy data from a series
of workbooks in the same folder into the appropriate worksheet in a
specific workbook, which is in the parent folder.
The workbook "PIP DD Template" has several sheets which correspond to
separate workbooks. There will be a workbook called "Permanancy Mod"
and a sheet in "PIP DD Template" with the name "Permanancy Records".
The "Mod" workbooks are all in a directory called "Records Mod" under
the directory called "SFY 06 Q1" which contains "PIP DD Template". The
macro will go through the "Mod" workbooks and copy the contents of
"Sheet1" to the appropriate sheet in "PIP DD Template".

Sub CopyModFile2PIP()
Dim sDirBase As String
Dim sFName As String
Dim lAns As Long
Dim sFileType As String
Dim sShtName As String
Dim oPIP As Object
Set oPIP = Workbooks("PIP DD Template.xls")

'Application.ScreenUpdating = False
lAns = MsgBox("Is the current Mod directory selected?", vbYesNo)
Select Case lAns
Case vbYes
sDirBase = "H:\AllDocs\CFSR PIP DD\SFY 06 Q1\Records Mod\"
sFName = Dir(sDirBase)
Do Until sFName = ""

sShtName = Left(sFName, Len(sFName) - 7)
sShtName = sShtName & "Records"
oPIP.Worksheets(sShtName).Select<------ERROR
Loop
Case vbNo
Application.ScreenUpdating = True
Exit Sub

End Select

Application.ScreenUpdating = True
End Sub

I'm getting a "select method failed" message at the marked line. So
what am I missing?
Thanks.