Consolidate all worksheets into master workbook

[jeffreybrown]

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?

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

 

[paulrit]

you can arrange all the 14 wrkbooks in single folder and use the below code. It will open all sheets take data and put into master sheet. Pls replace fpath=(give your folder path)

Sub copy_to_master2()
On Error GoTo aaa
Application.ScreenUpdating = False
Dim fpath As String, f As String, sht As Worksheet, wbk As Workbook
fpath = "************Folder Path**********"
f = Dir(fpath & "*.csv")
Do While Len(f) <> 0
Set wbk = Workbooks.Open(fpath & f, False, True)
For Each sht In wbk.Worksheets
sht.UsedRange.Copy
lastrow = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
ThisWorkbook.Sheets(1).Range("A" & (lastrow + 1)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Next sht
aaa:
Workbooks(f).Close False
f = Dir()
Loop
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

 

[jordax]

Cool, thanks! Let me try this out and get back to you!

 

[jordax]

OK now i am truly confronted with the depths of my ignorance in this subject ...

I tried to create a sample data set to try out the code you gave me, but it doesn't seem to be working. Here is what I did:

- created 3 dummy workbooks, each with 3 worksheets - identitical format, names and data for the sheets
- put these 3 dummy workbooks in one folder
- opened a new workbook , saved it outside the folderand pasted the code you gave me in the Visual Basic Editor
- F5 to run the code

... but nothing happens $

Thanks again!!

 

[paulrit]

did u put the folder path correctly, pls put in below format

fpath = "C:\Documents and Settings\paull\Desktop\MA-JUNE\"

also press alt+f8 to run macro

 

[jordax]

Yup!
I copied the formula exactly as you had it, but changed the file path.

Are my other steps correct though? I created a new, empty workbook outside the folder, then Tools>Macro>Visual Basic Editor, then Insert>Module, which is where I pasted the code.


Sub copy_to_master2()
On Error GoTo aaa
Application.ScreenUpdating = False
Dim fpath As String, f As String, sht As Worksheet, wbk As Workbook
fpath = "C:\Documents and Settings\yang.ong\Desktop\AKAM 2009\Monthly reporting consolidation\Testing\Entities\"
f = Dir(fpath & "*.csv")
Do While Len(f) <> 0
Set wbk = Workbooks.Open(fpath & f, False, True)
For Each sht In wbk.Worksheets
sht.UsedRange.Copy
lastrow = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
ThisWorkbook.Sheets(1).Range("A" & (lastrow + 1)).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Next sht
aaa:
Workbooks(f).Close False
f = Dir()
Loop
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub