In order to consolidate report files into one without copy-pasting is very easy. In fact, I’ve been using this trick ever since I was tasked to summarize the reports of 600+ schools in our city.
Imagine handling more than 600 files and coming up with a single report to explain the data. This task would normally take weeks or even months to accomplish.
But if all the files are available and are ready for consolidating, this would only take me a day to finish. This includes finalizing a chart dashboard report that is both flashy and corporate looking.
I’ve uploaded a YouTube video tutorial to explain this trick in the simplest manner. You can find the video below and see how fast I can consolidate 40 files into one.
The VBA Codes I use for this trick is available below. Since our website restricts copy-pasting due Intellectual Property Rights (IPR), a download link is provided for all of you by clicking here.
Sub GetSheets() 'Code Print https://ischoolforms.tech 'ref Integrated School Forms 2019 Path = "" FileName = Dir(Path & "*.xlsx") Do While FileName <> "" Workbooks.Open FileName:=Path & FileName, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(FileName).Close FileName = Dir() Loop End Sub
If you have any other questions or comments regarding this Consolidate Report Files trick, then please write your comments below.