Microsoft Office Forums - View Single Post - [Solved] Macro to open Multiple files and copy information to a master file

OfficeNewb · 04-08-2016, 08:28 AM

Hello gbaker -

Below is a quick example of copying data from external text files into a master Excel Workbook.

Code:

 
Sub ImportMonthlySales()

    Dim myTextFile As Workbook
    Dim OpenFiles() As Variant
    
    OpenFiles = GetFiles()
    Application.ScreenUpdating = False
    
    For i = 1 To Application.CountA(OpenFiles)
        Set myTextFile = Workbooks.Open(OpenFiles(i))
        
        myTextFile.Sheets(1).Range("A1").CurrentRegion.Copy
        Workbooks(1).Activate
        Workbooks(1).Worksheets.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
        myTextFile.Close
    Next i
    
    Application.ScreenUpdating = True
    
End Sub
 
Private Function GetFiles() As Variant
    
GetFiles = Application.GetOpenFilename(Title:="Select File(s) to import", MultiSelect:=True)
 
End Function

Running the ImportMonthlySales() macro will prompt the user to select the file(s) to be imported. Once the file(s) is found the macro will then import the data to a worksheet in the active Excel workbook.