In this blog post I will demonstrate how to combine data sets from multiple worksheets (of the same workbook) into a separate worksheet of that workbook. In other words, the goal is to combine the data set up to the point where a blank row is reached. We also want to exclude repeating headers.
Consider the data set below located in a tab named ST1 .
The second tab of the workbook (i.e. Tab ST2) contains a data set with the exact same fields but different valuses.
Note that in both tabs, the data begins from the fifth row and on that basis I have created the following code to combine the two tabs (i.e. ST1 and ST2) into a third tab named “Main”.
Insert the code above in the “Module” and “This Workbook” sections in “Developer”>”Visual Basic” section of the ribbon
Here is the output after the code is executed: