jeffreybrown
Well-known Member
- Joined
- Jul 28, 2004
- Messages
- 5,152
Hi All,
I think this needs a For Each loop, but I am stuck on how to get it accompished. In the folder I will have about 26 workbooks with all different titles, but the beggining is always the same for the workbook and the worksheet (Bldg). There will be only one ws per wb and the wb I am using to collect all of the worksheets is Final.xls. The code attached works fine, but the only way right now I know how to capture the next wb/ws is with a call. Can somebody help with how to loop through all wbs and return the ws?
I think this needs a For Each loop, but I am stuck on how to get it accompished. In the folder I will have about 26 workbooks with all different titles, but the beggining is always the same for the workbook and the worksheet (Bldg). There will be only one ws per wb and the wb I am using to collect all of the worksheets is Final.xls. The code attached works fine, but the only way right now I know how to capture the next wb/ws is with a call. Can somebody help with how to loop through all wbs and return the ws?
Code:
Sub CopyAllWrksht()
Dim MyPath As String, MyFile As String
On Error GoTo Error_Handler
MyPath = ActiveWorkbook.Path & "\"
MyFile = "Bldg LBV.xls"
If Dir(MyPath & MyFile) = Empty Then
MsgBox "The file " & MyFile & " was not found", , "File Doesn't Exist"
Exit Sub
End If
Workbooks.Open Filename:=MyPath & MyFile
Sheets("Bldg LBV").Copy After:=Workbooks("Final.xls").Sheets(1)
Workbooks(MyFile).Close
Exit Sub
Error_Handler:
End Sub
ffice
> </o
