I currently have 10 Microsoft Excel Worksheet files (.xlsx) each containing multiple sheets. Each .xlsx file contains information about college class enrollment for a particular semester of a particular year. Each file contains 7 different sheets all with different information, the last sheet being a Summary. All of my 10 files are formatted in the exact same way. The sheet that I am interested in occupies data from A7:J14.
My objective is to combine the data from the Summary sheet from each of these .xlsx files. I have looked at a number of different macros and thread postings on ExcelForum and have found that they only run on Excel Workbooks (don't accept .xlsx files). The macros I have looked at allow the user to select which files they want to gather data from (files that are closed, not open) and then output that combined data in the form of a pivot table. This is exactly what I want to do. I am struggling to figure out two things: 1. How to use these macros that work on Excel Workbooks so they work on .xlsx files. 2. How to use these macros so they can specifically take information from the specific sheet (1 sheet among the 7) in each file.
Thank you in advance, to anyone that provides information on the problem I'm running into. I looked at Kirill Lapin's solution and another solution somewhere here in ExcelForum (sorry that's not much help).
Bookmarks