What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook.
I wanted however to account for 2 typical scenarios: Sub Merge Excel Files(file Names() As String, Optional worksheet Name As String = vb Null String, Optional merged File Name As String = "merged.xlsx") Dim file Name As Variant, wb As Workbook, ws As Worksheet, dest Wb As Workbook, excel App As Application Set excel App = New Application Set dest Wb = excel App. Add For Each file Name In file Names Set wb = excel App. Open(file Name, Read Only = True) For Each ws In wb.
This particular culprit has somewhere in the region of 20 tabs relating to a particular care type – such as outpatients, admitted patient care, A&E etc, we then get 5 copies of this for each PCT.
Multiply this by 12 for each month of the year and you start to have quite an horrendous copy and paste task if you want to pull it all together manually.
Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx). As we go along we modify our VBA Array redefining its size and adding additional items. One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge.