Combine multiple Excel workbooks into a single workbook

by

The following accomplishes the task.

Option Explicit

Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Dim WrdArray() As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "c:\test\"
fileName = Dir(directory & "*.xl??")

Do While fileName <> ""
    Workbooks.Open (directory & fileName)
        WrdArray() = Split(fileName, ".")
        For Each sheet In Workbooks(fileName).Worksheets
        Workbooks(fileName).ActiveSheet.Name = WrdArray(0)
            total = Workbooks("import-sheets.xlsm").Worksheets.Count
            Workbooks(fileName).Worksheets(sheet.Name).Copy after:=Workbooks("import-sheets.xlsm").Worksheets(total)

            GoTo exitFor:

        Next sheet

exitFor:
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

More Related Contents:

  1. VBA macro that search for file in multiple subfolders
  2. So, I have 6 “master” files to then divide into 40 separate files
  3. Getting ScriptControl to work with Excel 2010 x64
  4. Application.Match gives type mismatch
  5. Count and Highlight keywords within phrases
  6. Transpose a range in VBA
  7. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  8. Detect merged cells in VBA Excel with MergeArea
  9. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  10. Parse HTML content in VBA
  11. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  12. How do I name a spreadsheet automatically, by referencing two cells?
  13. Excel VBA Delete Rows
  14. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  15. Should I turn .CutCopyMode back on before exiting my sub procedure?
  16. VBA: How to delete filtered rows in Excel?
  17. Determine whether user is adding or deleting rows
  18. How to use VBA SaveAs without closing calling workbook?
  19. Round function in Excel, worksheet function vs VBA
  20. How to continue the code on the next line in VBA
  21. Excel VBA Userform – Execute Sub when something changes
  22. Count the number of rows in another sheet [duplicate]
  23. VBA Password Input with Cancel Function
  24. Finding first blank row, then writing to it
  25. Calling regasm without administrative rights for COM interop in Excel VBA
  26. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  27. How do I activate a specific workbook and a specific sheet?
  28. Start VBA macro when editing a cell
  29. VBA Run-time error 438 appears when “paste” runs
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment