Page 1 of 6 123 ... LastLast
Results 1 to 10 of 58

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

This is a discussion on Using VBA scripts to Combine multiple workbooks of single worksheet to a single workbook of multiple worksheets within the Excel Questions forums, part of the Question Forums category; I am a beginner to Excel and VBA , can somebody show me a few lines of scripts and instructions ...

  1. #1
    New Member
    Join Date
    Jan 2008
    Posts
    4

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

    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

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,924

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

    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.

    Code:
    '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.

    Code:
    '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.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,576

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

    This will combine workbooks with a single sheet into a new workbook with multiple sheets.
    Code:
    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
    If posting code please use code tags.

  4. #4
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,924

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

    Sorry, yes mine combines them all into one sheet.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  5. #5
    New Member
    Join Date
    Jan 2008
    Posts
    4

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

    Thanks to schielrn, Norie and everyone who helped or looked into this to help.

    It turns out that my problem is slightly more complicated, the source Excel files have more than 1 worksheet. But the worksheets I am trying to copy are always the 1st worksheet of a xls file. E.g. I have 10 source xls files, each one of them has 5 worksheets, I am trying to copy the 1st worksheet of each xls file, that results to one single xls file - with 1 workbook of 10 worksheets.

    It should be similiar to what schielrn and Norie's codes, but what needs to be change, please suggests.

    Thanks very much again.
    Excel_beginner

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,576

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

    The code I posted should do that.

    This creates a reference to the first worksheet.
    Code:
    Set wsSrc = wbSrc.Worksheets(1)
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Jan 2008
    Posts
    2

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

    I don't mean to hijack this thread but I have a similar problem. The code above works great if all of the files are in the same folder. However in my case each file is in a different folder. The folders are named a01, a02, a03, b01, b02, etc. Any ideas? I don't even mind if I have to pick each of the files as this is something I will only have to run once a month. I just need to code to be able to pick a file from a different folder. Any help would be greatly appreciated.

    Thanks!

  8. #8
    Board Regular
    Join Date
    Jun 2007
    Posts
    66

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

    Norie,

    Your post is exactly what I am looking for, almost! I had success, but instead of combining into a new workboook, I need to combine into an exisiting workbook if possible.

    Thanks,

    Danny

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,576

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

    Danny

    Well if that's the case you need to alter this line.
    Code:
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    What it does is create a new workbook and a reference to it to use in the subsequent code.

    So what you need is something like this.
    Code:
    Set wbDst = Workbooks("WorkbookToAddSheetsTo.xls")
    Obviously you'll need to change the name, and the workbook in question must either be open or you must use code to open it.
    If posting code please use code tags.

  10. #10
    Board Regular
    Join Date
    Jun 2007
    Posts
    66

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

    The worksheets within the folder are brought into the open workbook, but any sheets within the workbook get delete. Is there a way to keep the existing sheets while bringing in the others?

    Danny

Page 1 of 6 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com