Using VBA scripts to Combine multiple workbooks of single worksheet to a single workbook of multiple worksheets

[Excel_beginner]

I am a beginner to Excel and VBA, can somebody show me a few lines of scripts and instructions how to use VBA scripts to combine multiple Excel xls Files (which contain single worksheet) into a single Excel file of multiple worksheets?

Can somebody also suggest a good book with examples I can start to learn to solve these kinds of problems?

Thanks very much

Excel_beginner

 

[petes]

This code is perfectly working for me!!!! The changes I need is:

1) To prompt for a dialogbox for selecting particular files
2) To accept different file formats (.xls, .xlsx...)

Could someone please help me

Thanks a lot!!!!!

This will combine workbooks with a single sheet into a new workbook with multiple sheets.

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\MyPath" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xls", vbNormal)
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
        
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close False
        
        strFilename = Dir()
        
    Loop
    wbDst.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

 

[wouldbeca]

This code is perfectly working for me!!!! The changes I need is:

1) To prompt for a dialogbox for selecting particular files
2) To accept different file formats (.xls, .xlsx...)

Could someone please help me

Thanks a lot!!!!!

For your second query it will do

strFilename = Dir(MyPath & "\*.xls*", vbNormal)

Regarding first one, waiting from response from other members.
</pre>

 

[petes]

Thanks @wouldbeca!!

can someone please provide me a solution on 1st question...I tried my best but could not find a way out

Thanks!!

 

[petes]

Any thoughts guys on 1st question??

 

[petes]

Everything is done except I need a dialog box to select the file instead of harcoding the location...

Please help me out!!!!!!!

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\SHI" ' change to suit
    Application.DefaultSaveFormat = 51
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xls*", vbNormal)
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
        
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close False
        
        strFilename = Dir()
        
    Loop
    wbDst.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

 

[Topposa]

Hi All,
I am completely new to VBA world. I got the code from this forum to merge worksheets from multiple workbook into one master workbook which was posted by schielrn back in 2007. See below code. The code works perfectly fine, but this code only merges sheet 1s into one master sheet1.

However, my requirement is bit extra -

I am using Excel 2013. I have 6 different workbooks with 4 worksheets in each workbook. All 4 sheets in a workbook contains different set of data but all sheet1s are same, all sheet2s are same, all sheet3s are same and all sheet4s are same.

I want to merge all 4 worksheets from all workbooks with 4 master worksheets creating one master workbook. Basically, I want all sheet1s from 4 different workbook to merge into one master Sheet1 and all sheet2s into one master sheet2 and same for sheet3s and sheet4s.

Please help. I’ll be glad if this can be worked out.

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook.Name
    
    path = GetDirectory("Select a folder containing Excel files you want to merge")

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
            Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
            Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
            CopyRng.Copy Dest
            Wkb.Close False
        End If
        
        Filename = Dir()
    Loop

    Range("A1").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
End Sub

 

[rbowey]

I have a similar but I think a little more complex issue that I need assistance with. I am trying to take multiple workbooks with multiple worksheets and compile the data from each worksheet into a corresponding worksheet in a master workbook. So, worksheet one of each workbook will be compiled into worksheet one of a master work book, next worksheet two of every workbook will be compiled into worksheet two of the master workbook. Now the trick is that worksheet one needs to give a total of all the data points in that worksheet whereas worksheet two needs to just add the list of data from each workbook to the master compiling a full list. Finally, the biggest problem I see is that currently the name of each workbook changes every week due to the change in date being part of the naming convention. We could probably change this but they would prefer not too if it can be worked around. Currently these compilations are being done manually and I am trying to automate the process to become more efficient. I have done similar things with Powerpoint slides but not at this scale of required detail. Any help would be appreciated.

 

[sailorjupitor]

Well this code works exactly for me and any issues I've been able to figure out by searching for specific problems related to any error messages.

 

[Webby1]

I'm new to Vba and after several attempts I can't get this code to work. I have 17 workbooks that each contain 23 worksheets I want to copy worksheet 19 from each work book on to one master file, one sheet. All the workbooks are contained in one folder. Can any one help please?

 

[Subi1980]

Hi Everyone,

I have gone through the entire post. It was very helpful.

I kindly request someone to help me out with a similar problem who has initiated this post.

My requirement is, I have multiple Excel Files (Say 20 files) with only one sheet (Sheet name: Data Extracted) saved in the same folder as the master file (file name: Consolidate from folder). The sheet will have approximately 10-15 columns and 23 rows (starting from Row 10).

All I want to do is, except the first two columns from all the files to be copied into Master file starting from column C (Because, the master file and all the files in the folder has same information in the first two columns (A & B).

So, once we run the macro, the first two columns should not be disturbed, and the information from all the files from column C till the data available needs to be pasted without any blank columns into master file.

For example, assuming we have 10 files and each file has 10 columns (except first two columns); Once we run the Macro, the final output in the master file (Consolidate from folder) should have 100 columns (except first two columns) and without any blank columns.

Apologies If its confusing.