Without getting too technical, the purpose of this article is to show you how you can loop through multiple files and a particular folder on your computer and scrape data from each worksheet.
We’re going to take this data and put it on to the master workbook, meaning the workbook where the code originates from.
Obviously, you can copy and paste this code or download the zip file that contains the three demo workbooks and the master workbook with all the code, but if you would prefer an over the shoulder video guide, just watch the YouTube video at the end of this post.
If you’re interested in learning the ins and outs of VBA, check out my ultimate Excel programmer course, which is now over 10 hours of jam packed Excel content!
[cc lang=”vbscript” lines=”-1″ width=”100%”]
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject(“Scripting.FileSystemObject”)
‘This is where you put YOUR folder name
Set fldr = fso.GetFolder(“C:\temp\”)
‘Next available Row on Master Workbook
y = ThisWorkbook.Sheets(“sheet1”).Cells(Rows.Count, 1).End(xlUp).Row + 1
‘Loop through each file in that folder
For Each wbFile In fldr.Files
‘Make sure looping only through files ending in .xlsx (Excel files)
If fso.GetExtensionName(wbFile.Name) = “xlsx” Then
‘Open current book
Set wb = Workbooks.Open(wbFile.Path)
‘Loop through each sheet (ws)
For Each ws In wb.Sheets
‘Last row in that sheet (ws)
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
‘Loop through each record (row 2 through last row)
For x = 2 To wsLR
‘Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
ThisWorkbook.Sheets(“sheet1”).Cells(y, 1) = ws.Cells(x, 1) ‘col 1
ThisWorkbook.Sheets(“sheet1”).Cells(y, 2) = ws.Cells(x, 2)
ThisWorkbook.Sheets(“sheet1”).Cells(y, 3) = CDate(ws.Cells(x, 3))
ThisWorkbook.Sheets(“sheet1”).Cells(y, 4) = ws.Cells(x, 4)
y = y + 1
‘Close current book
Watch the explainer on YouTube below:
You Might Also Like
If you've ever encountered a corrupt Excel workbook, you've probably tried lots of things already, including possibly tearing your hair out. In this particular scenario, we have lots of extra sheets that show up in the Visual Basic Editor. This is not great. ...and it...
Sometimes in Excel, we need to automate simple tasks, like to create sheets automatically for each item in a range. If you just had a few worksheet tabs that needed to be created, it wouldn't be a big deal, but what if you had to create 100 different worksheet tabs?!...
Date Picker Calendar in Excel VBA Oftentimes, users want to click a button and select a date. This is no different for Excel developers. Check out this ActiveX control by Microsoft that allows users to do just that. It's a little old school looking, but actually has...
Check out how Jiyaad is able to save 5+ hours biweekly! He's using the Complete Web Automation with Excel VBA and Selenium course, jam packed with 8+ hours of videos.Want to see Free Previews? Click here.Get Lifetime Access to the course by using coupon code 'EVBAJYD'...
In order to highlight text in a TextBox (other than Tabbing into that field), you can insert a couple helpful snippets of VBA code that will help you accomplish this easily! The first line tells VBA to put the cursor at the leftmost (0) character:...
We had a question today regarding how to increase or decrease the date field in this Excel VBA user form. The first thing we had to do was create a user form and insert a Text Box. When we double-click on this text box, the default event is the change...