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

 

[cbaker86]

See post 14, for the getdirectory() function.

schielrn, Code works great but won't ruin when I select a directory that is on our server (only works for local hard drive directories). Is there any additional code I can incorporate to get this running?

 

[ateam505]

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

This code is pretty much what I'm looking for, however, can this code be modified so that only values and formats are copied over to the new master workbook? I don't want the formulas, which reference other workbooks and worksheets to copy over.

Thank you.

 

[Tipperd]

Completely new to VBA....I am wanting to put all workbooks in one file folder into one master workbook on one sheet. schielrn's post on Jan 18th, 2008, VBA for no rows looks good. But I am very new at this and I don't know what the items that need changed should look like.

The folder is C:\Users\!!!!!\Desktop\Financials and there are 21 workbooks. Each workbook has a worksheet that has a different name due to the export process. However all the columns and data are similiar. But each workbooks worksheet may have more than another depending on the amount of financial activity so I don't know what the range is from month to month.

Any training or scripting would help.

Thanks

 

[boombox]

Try this:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=829

This works great, but as I have it installed in my personal macro workbook, I'd like the ability to use this in a new, blank workbook. Any tips?

Thanks!

 

[boombox]

This works great, but as I have it installed in my personal macro workbook, I'd like the ability to use this in a new, blank workbook. Any tips?

Thanks!

Polite bump.

 

[eggsell22]

Generally the personal workbook is hidden and you should be able to execute this code in another workbook without a problem. If you want to specifically save it to another workbook copy the code and paste it into a module within the new workbook.

 

[OmGanesh82]

Hi

I need a code so that i can combine multiple workbooks placed in a folder to a single workbook. Each of the workbooks have same headers.

Do not know VBA and till now I was using copy paste and vlookup.

Plzzzzzzz hlp.

 

[Camargo]

Hi Norie! I have been using your code and it works well! But, when it finishes the process, excel presents a message box: runtime error '440' automation error.
Despite this message, the file generated is correct. I validated it.
How can I solve this problem? Can this kind of error generate bad results?

Thanks in advance!

Camargo

 

[sharma9187]

Hi,

I have a excel file with multiple sheets. I made 3 copies of that file and Currently 3 users are updating those 3 files. Is it possible to combine those 3 excel files in a single file with all those sheets. I dont want separate sheets to be created for each file. It should update in the corresponding sheets one row after another except blank rows.
Could you please help in programming this scenario.

Thanks in advance.

Note: The below example worked like a charm and I managed to use effectively. Thanks a lot.

Are these workbooks all stored in the same folder? Are there other workbooks in this folder what will not be combined into one worksheet? If so, you could try this, but I would highly recommend making a complete copy of the folder you are trying to use this one. They both are very similiar and will ask for the directory to copy from. Just copy this into your master workbook and then choose the directory and off you should go.

Version 1 takes all 1st sheets (can have blank rows) and puts into a master workbook.

'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

Version 2 takes all 1st sheets (cannot have blank rows) and puts into a master workbook.

'Description: Combines all files 1st sheet in a folder to a master file
Sub MergeFilesWithoutSpaces()
    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

    ThisWB = ActiveWorkbook.Name
    
    path = GetDirectory("Select a folder containing Excel files you want to merge")
    
    RowofCopySheet = InputBox("Enter Row to start copy on") ' Row to start on in the sheets you are copying from

    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(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
            Set Dest = shtDest.Range("A" & shtDest.Cells(Rows.Count, 1).End(xlUp).Row + 1)
            CopyRng.Copy
            Dest.PasteSpecial xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False 'Clear Clipboard
            Wkb.Close False
        End If
        
        Filename = Dir()
    Loop

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

Hope that helps. Sorry I don't know of any books to recommend as I have not read any myself. My learning has been through a visual basic class in college and this board and then trial and error. But the class was by far my most help at getting started.

 

[sharma9187]

Hi,

Excellent piece of work. Your macro saved lot of time for me. I have a simillar requirement for another excel file and request your help with a macro. The scenario is as follows.


I have a master excel file template, I have made 3 copies of it. One file for each user. About 10 sheets are present in each file with same name, same formatting, same column names.
Each user enter their data in their respective files. I want to combine each sheet in user file to master sheet.

For ex.

I have sheet name 1

In master sheet If I run a macro, it should combine the data from Sheet named 1 from all the 3 files to master sheet named 1.

Could you please help me on this. I was in search of many places for a solution to this but no one was able to help me out. Seek your help on this.

Thanks in advance.

Are these workbooks all stored in the same folder? Are there other workbooks in this folder what will not be combined into one worksheet? If so, you could try this, but I would highly recommend making a complete copy of the folder you are trying to use this one. They both are very similiar and will ask for the directory to copy from. Just copy this into your master workbook and then choose the directory and off you should go.

Version 1 takes all 1st sheets (can have blank rows) and puts into a master workbook.

'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

Version 2 takes all 1st sheets (cannot have blank rows) and puts into a master workbook.

'Description: Combines all files 1st sheet in a folder to a master file
Sub MergeFilesWithoutSpaces()
    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

    ThisWB = ActiveWorkbook.Name
    
    path = GetDirectory("Select a folder containing Excel files you want to merge")
    
    RowofCopySheet = InputBox("Enter Row to start copy on") ' Row to start on in the sheets you are copying from

    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(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
            Set Dest = shtDest.Range("A" & shtDest.Cells(Rows.Count, 1).End(xlUp).Row + 1)
            CopyRng.Copy
            Dest.PasteSpecial xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False 'Clear Clipboard
            Wkb.Close False
        End If
        
        Filename = Dir()
    Loop

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

Hope that helps. Sorry I don't know of any books to recommend as I have not read any myself. My learning has been through a visual basic class in college and this board and then trial and error. But the class was by far my most help at getting started.