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]

In Excel, open the macro screen (Alt-F8) and double click on the macro.

 

[John44]

May be getting somewhere. Keep getting this message which i have to click through

 

[petertenthije]

Could you replace the earlier code with this:

Sub Identify_source_files()

Application.DisplayAlerts = False
 
    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("Combine Files MK.xlsm").activate
' Add new worksheet
    Sheets.Add After:=ActiveSheet
' Paste data
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
' Close source file
    Sheets("List with source files").Select
    Workbooks(Range("A" & i).Value).Close SaveChanges:=False


Next oFile

End Sub

 

[John44]

Ok. It starts working and then my excel stops working after about 10 seconds. Could it be related to all the other macros that appear that are related to an 'add ins' menu I use?

 

[petertenthije]

Ok. It starts working and then my excel stops working after about 10 seconds. Could it be related to all the other macros that appear that are related to an 'add ins' menu I use?

I am sorry, can't help you with that. I am not that familiar with add-ins.

 

[John44]

I need to email someone the spreadsheet. Would they get all those macros? I guess they would only need one. Alt F8 shows loads of macros in all other places. I will try to reduce the number of files and then see if it works

 

[John44]

Thank you for your time with this. It can do about 15 files and then I get this message and then it crashes.