Here’s the two-step process in slow-motion: Set rngSrc = rngSrc.Offset(1, 0).Resize( – 1) On the first loop, we want to include the headers, but each subsequent time we do NOT want to include the headers.Įach loop after the first, we adjust rngSrc to skip the first row like this: Why are we checking to see if this is the first loop? On lines 60-62, we check to see if this is NOT the first iteration. Here’s where things get a bit more interesting… This is another great checkpoint: we can verify that all the data has been correctly identified and stored in rngSrc by un-commenting out lines 53-55 and calling rngSrc.Select to highlight all the cells.
HOW TO MERGE WORKBOOKS INTO ONE WORKSHEET FULL
With the last-occupied row and last-occupied column numbers stored in lngSrcLastRow and lngSrcLastCol respectively, we store the full data range on lines 48-51, starting from the top-left corner and extending to the bottom-right.
This is critical here! By dynamically determining the last column and last row on each loop, we can be confident that we’re getting all the data from each Worksheet. Line 42 assigns the target worksheet, named “data” in this example, to wksSrc.Įxploration continues on lines 46-47, where we take advantage of the LastOccupiedRowNum and LastOccupiedColNum functions (which are defined at the very bottom as well as in the VBA Toolbelt, which you should be using) to easily identify the last-occupied row and last-occupied column on the source Worksheet. We’ll immediately take advantage of that full file path to the Excel file on line 41, where we open that Workbook and save a reference to it as wbkSrc (where “Src” is short for “Source”). On line 38, strFilePath is assigned to be the original source folder string ( strDirContainingFiles, which is “C:\blog\example_data_in_here” in this example), a backslash (“\”), and the file name from colFileNames. With those file names stored neatly in colFileNames, we begin looping through it on line 35. The “use a Collection” strategy also makes it really easy to verify that the loop worked and pulled in the data we expected, which you can check for yourself by un-commenting lines 27-31. Programming is hard enough - avoid deeply-nested loops whenever you can. Each nested loop you add is another layer of complexity for you to mentally keep track of… fuck that.
HOW TO MERGE WORKBOOKS INTO ONE WORKSHEET CODE
Yes, we COULD have conducted the bulk of the code inside this Dir loop, but I prefer using a Collection here because it reduces the number of nested loops in our subroutine. Umm why not do the whole thing in this Dir loop? Lines 22 through 25 store each file name inside a Collection (named colFileNames), which will make it SUPER easy to iterate through each file a little bit later in the code. (That’s what the asterisk character, “*”, is doing there at the end of the line.) Step 2 – Exploration begins on line 21, where we take advantage of the Dir function to loop through the directory we set up moments ago ( StrDirContainingFiles) and identify every file that ends in “.xlsx”. output) to store the data from each individual file, then assign the first Worksheet in that Workbook as the Dst Worksheet. Then, on lines 16-17, we create a new Workbook (where Dst is short for “destination”, i.e. (You’ll want to change this to your folder, but in this example we are targeting C:\blog\example_data_in_here.) Our Step 1 – Setup is covered by lines 16-18 - short and sweet.įirst, on line 16, we assign the folder name where the individual Excel files are stored.
Let’s break this challenge down step-by-step using the 4-step VBA process as our guide: Here’s a link to the code above so you can review it side-by-side with the walk through below. This situation blows, since your analysis depends on a pivot table and combining a bunch of workbooks together by hand is terrible.įortunately, VBA will make short work of this pain in the ass. I need to make a pivot table but the data is spread out in lots of different Excel files…