Posted this earlier, but didn't get any satisfactory answer to my
problem.
Here's the code I've written so far:

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

Am getting a "Select method fail" error at the marked spot. Any ideas?
Thanks.

If that sShtName actually exists in the oPIP workbook (I'd double check that
first), then you can't select a worksheet in a workbook that's not active:

opip.activate
oPIP.Worksheets(sShtName).Select

Maybe????



davegb wrote:

Posted this earlier, but didn't get any satisfactory answer to my
problem.
Here's the code I've written so far:

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

Am getting a "Select method fail" error at the marked spot. Any ideas?
Thanks.

--

Dave Peterson

Dave Peterson wrote:
If that sShtName actually exists in the oPIP workbook (I'd double check that
first), then you can't select a worksheet in a workbook that's not active:

opip.activate
oPIP.Worksheets(sShtName).Select

Maybe????

Activating it did the trick! Thanks, Dave!



davegb wrote:

Posted this earlier, but didn't get any satisfactory answer to my
problem.
Here's the code I've written so far:

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

Am getting a "Select method fail" error at the marked spot. Any ideas?
Thanks.

--

Dave Peterson