gaqalpine.blogg.se

(line 41) Check dicFinalHeaders to see if it already contains this column name (i.e.(line 40) Assign strColHeader to be the leading-and-trailing-spaces-removed column header name.Inside this loop, we will repeat the next 4 steps for each column header: Line 36 kicks off a For…Next loop through this Worksheet’s occupied-columns, which is where our Step 3 – Execution takes place for Phase 1. You’re using the VBA Toolbelt, right? Please download it, use it as your new project template, and save yourself TONS of repetitive coding…īut let’s move on, as our Step 2 – Exploration for Phase 1 is done! Once we are sure we are NOT on the Destination Worksheet, we identify the last-occupied column on line 35 using LastOccupiedColNum from the VBA Toolbelt. The Step 2 – Exploration of Phase 1 takes place between lines 26-40.įirst, we start looping through all of the Worksheets in ThisWorkbook on line 26, ignoring the Destination Worksheet ( wksDst) on line 29. Phase 2: copy each column from each Worksheet to the appropriate place on our Destination Worksheet.Phase 1: assemble the final headers Scripting.Dictionary and prepare the Destination Worksheet.Smooth! With our set up out of the way, we’ll accomplish Step 2 – Exploration and Step 3 – Execution in two phases: (line 18) Create a new Worksheet and set it to wksDst - this will be our Destination Worksheet, where all of the data will be combined.

Count of dicFinalHeaders, because we will need to know when new columns are added (and will use this variable for comparisons)

(line 15) Make sure the Scripting.Dictionary is set to vbTextCompare, which means the Keys will be case- INsensitive.Step 1 – Setup is a cinch, and we knock it all out on lines 14-18. Let’s review the code 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.

Here’s the scoop y’all - our It’s Always Sunny sales data can be combined with this macro: Phew! Now we can get back to the task at hand… combining data!