Select failed?
 

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.

Select failed?
 

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

Select failed?
 

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