Macro to open Multiple files and copy information to a master file

gbaker · #1 04-05-2016, 12:07 PM

Hi All,
Looking for some code that would allow me to open multiple excel sheets and copy the information to a Master File. I set up a macro in Outlook that whenever the files ar received it put them in a folder on y network.

Here is an example of the data in the files that I want to copy to the Master:
Job No PackageCode File Name Date Dropped Quantity Status
461332 E-XUL E-XUL_5014605A1-0001-.PS 4/4/2016 15:17 14 Confirmed
461333 E-XUL E-XUL_5014605A1-0002-.PS 4/4/2016 15:17 177 Confirmed
461334 AFA-XUL AFA-XUL_5014605A1-0003-.PS 4/4/2016 15:17 311 Confirmed
461335 ING-XUL ING-XUL_5014605A1-0004-.PS 4/4/2016 15:17 190 Confirmed
461336 FE-XUL FE-XUL_5014605A1-0005-.PS 4/4/2016 15:17 17 Confirmed

OfficeNewb · #2 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.

gbaker · #3 04-08-2016, 08:44 AM

Hi,
Thanks for the help. I need to have the code go to a specific folder as soon as it runs and be able to open multiple files, copy and paste the information to a master table.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run a macro on multiple docx. files	Peter Carter	Word VBA	27	12-15-2022 04:10 PM
Error when trying to open .docx after I run a macro to copy data between tables in two documents	reesjordan	Word VBA	3	06-18-2015 12:28 AM
Macro to copy cell info to multiple documents	Patrick Innes	Word VBA	2	02-18-2015 08:38 PM
how to copy some information from one excel file to another excel file	tomlam	Excel Programming	4	10-01-2013 03:06 PM
looking for macro for multiple files	bolk	Word	3	05-03-2011 05:46 AM