import data from multiple closed workbook to workbook based on headers

[abdelfattah]

hello
I have five workbooks each workbook contain data in sheet1 begins range from a1:e the headers are (code , batch, brand ,purchasing ,selling ) should import data to workbook name is report and copy to sheet1,2,3,4,5 begins from a1:e and if change data in workbook1,2,3,4,5 then update in workbook "report" and I want import data without open the five file
thanks

 

[Saurabhj]

Hi, Power Query is good solution for the requirement.

And you can update the data whenever you open the "report" workbook.

you can check this video for Power Query (Click Here)

 

[abdelfattah]

thanks but I prefer by vba code actually I don't used to by power query

 

[Saurabhj]

Hi,

Check below code:

Assuming that Report file and other 5 files are in same folder.

Sub copySelected()
    Dim myfile As Workbook
    Dim filenames, sheetnum As Integer
    
'mention files name
    filenames = Array("file1.xlsx", "file2.xlsx", "file3.xlsx", "file4.xlsx", "file5.xlsx")
    Application.ScreenUpdating = False
    
    For sheetnum = 0 To 4
        Set myfile = Workbooks.Open(ThisWorkbook.Path & "\" & filenames(sheetnum))
        myfile.Activate
        myfile.Sheets("sheet1").Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Sheets(sheetnum + 1).Range("A1")
        myfile.Close
    Next
    Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

thanks the data is copying as what I want but it gives me error after run the code subscript out of range in this line

        Set myfile = Workbooks.Open(ThisWorkbook.Path & "\" & filenames(sheetnum))

 

[Saurabhj]

Hi, Subscript Out of Range error occurs if VBA didn't find the specified file.
1. Please check the filenames provided.
2. The loop will run 5 times so make sure 5 filenames are available in the array.

 

[abdelfattah]

Hi, Subscript Out of Range error occurs if VBA didn't find the specified file.
1. Please check the filenames provided.
2. The loop will run 5 times so make sure 5 filenames are available in the array.

you're extremely right many thanks for your code , just i'm asking about if you can mod the code to merge duplicated data and sum values
the duplicated items based on column a all of the files and the values in column e all of the files
thanks again