Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
Select other workbook to select data in a macro. | Excel Programming |