import data from multiple closed workbook to workbook based on headers

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
423
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
thanks but I prefer by vba code actually I don't used to by power query ;)
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
423
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Check below code:

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

VBA Code:
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
 
Solution

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010

ADVERTISEMENT

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
VBA Code:
        Set myfile = Workbooks.Open(ThisWorkbook.Path & "\" & filenames(sheetnum))
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
423
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,197
Messages
5,640,795
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top