Can't select
 

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.

Can't select
 

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.

Can't select
 

Gary''s Student wrote:
Immediately before the .Select command put a

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

I did this, but it just showed the name. It is a sheetname, but I don't
think this proves anything. I had already done a Watch to see if the
variables are properly populated, and they are. Any other suggestions?



"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.

Can't select
 

davegb,
I believe it's because you cannot .Select an object, unless it is active.
Try adding a "oPIP.Activate" before the problem line
But do you to .select at all ?

NickHK

"davegb" wrote in message
oups.com...
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.

Can't select
 

NickHK wrote:
davegb,
I believe it's because you cannot .Select an object, unless it is active.
Try adding a "oPIP.Activate" before the problem line
But do you to .select at all ?

NickHK

Thanks for your reply, Nick, that was the problem. And the select was
just a test to make sure the code is working up to this point. I'm
actually going to copy data from the source sheet up to the target
sheet, just wanted to be sure I had both coded correctly before I start
copying and pasting data.


"davegb" wrote in message
oups.com...
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.