Thread: Consolidate Multiple Worksheets

Hello all,

After reviewing the forum I have found code that I can modify to consolidate sheets within a given folder, although it will select all Excel files.

I have also located code that can multi-select worksheets and open them. I just need to merge the two but do not know how.

I'd like to select multiple worksheets in a dialog box (GetOpenFileName), etc, then consolidate them to my "Consolidated" worksheet in the active workbook.

The code I found is as follows:

Open multiple files:

Code:

Sub OpenMultipleFiles()
Dim fn As Variant, i As Integer
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For i = 1 To UBound(fn)
Debug.Print "Selected file #" & i & ": " & fn(i)


'put your code to manipulate the text here

Next i

End Sub

Consolidate sheets in a given directory:

Code:

Sub CollateReportFromFiles()
'Open all .XLS in specific folder (2007 compatible)
Dim strFileName As String, strPath As String, MyVal As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
    
Application.EnableEvents = False
Application.DisplayAlerts = False
    
Set wbkNew = ThisWorkbook
strPath = "C:\Documents and Settings\"
strFileName = Dir(strPath & "*.xls")
wbkNew.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws

ActiveSheet.Name = "Final"

    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        MyVal = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
        wbkOld.Sheets(MyVal & ".xdo").Activate
        'Copy After:=wbkNew.Sheets(Sheets.Count)
        ActiveSheet.Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
wbkNew.Sheets("Final").Delete
Application.DisplayAlerts = False
Application.EnableEvents = True
End Sub

Where am I to start on this one?

Thank you for your help!

Does anyone know another way to combine the two codes?