Combining excel files

[John44]

Hi,

I get sent about 100 excel files of products sold and most have only a few lines of excel. What I would like to do is have one excel files with a hundred tabs. Is there a way I can quickly do this as cutting and pasting all these files into tabs is so time consuming.

Thanks

John

 

[petertenthije]

Do the Excel files have any logic in their file names?

 

[John44]

Like this with the date field bit changing.

 

[petertenthije]

This should work.

Please open a new XLSM file, and create a worksheet named "List with source files".
Add this macro to the XLSM file.
Save this XLSM file in a DIFFERENT folder then the folder with your source files.
In the macro, change the path behind "Folderlocation", to match the path with your source files.

Sub Identify_source_files()

 
    Sheets("List with source files").Select
    Columns("A:A").ClearContents
    Range("A1").Select

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer

' This is the location of the source files!
' Do not put the macro file in the same folder!
FolderLocation = "C:\Users\peter\OneDrive\Documents\MR Excel\OpenPO"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(FolderLocation)

For Each oFile In oFolder.Files

' Identify the source file
    Cells(i + 1, 1) = oFile.Name
    i = i + 1
' Open the source file
    Dim wkb As Workbook
    Set wkb = Workbooks.Open(FolderLocation & "\" & Range("A" & i).Value)
' Copy the contents of the source file
    ActiveSheet.UsedRange.Copy
' Return to the master file
    Workbooks("OpenPO.xlsm").Activate
' Add new worksheet
    Sheets.Add After:=ActiveSheet
' Paste data
    Range("A1").Select
    ActiveSheet.Paste
' Close source file
    Sheets("List with source files").Select
    Workbooks(Range("A" & i).Value).Close SaveChanges:=False


Next oFile


End Sub

 

[John44]

which one is XLSM? Is it the normal excel or macro-enabled? Then how do I add the code to it?

 

[petertenthije]

The macro enabled file is XLSM.

To add the code, go to visual basic (Alt-F11).
Insert a new module
In the new module, paste the VBA code I provided earlier.

 

[John44]

Ok done that. There were loads of other macros for add ins etc and boxes opening. Now what do I do?

 

[John44]

 

[petertenthije]

In the macro, please change:

Workbooks("OpenPO.xlsm").activate
to
Workbooks("Combine Files MK.xlsm").activate

Should have mentioned that in my first post.
After that you can run the macro.

 

[John44]

How do I run the macro?