Excel Combine worksheets into one

Bobby12345 Posts 1 Registration date Thursday March 30, 2017 Status Member Last seen March 30, 2017 - Updated on Mar 30, 2017 at 04:50 PM
 Blocked Profile - Mar 30, 2017 at 04:49 PM
Newbie here,

I have a question on how to do this in excel. I have lots of worksheets, 200+, each with different names, and I was wondering if its possible to copy data and merge them into one sheet, with certain headers on the top instead of the template I have right now. I have posted images below to show what the worksheets look like, and what I want my final worksheet to look like. The worksheets all have the same format, with information in the same cells, but they may or may not be blank. I have posted what one worksheet looks like below.

(this is the last cell that may have information, all the worksheets have this exact same format)

Final worksheet, the headers continue on to the top now:

1 response

Blocked Profile
Mar 30, 2017 at 04:49 PM
Yes it is possible.

Initialize a variable(finalsheet) that points to the Final sheet.

Initialize a variable (worksheetcount) and set it to 0.

Now, loop a FOR EACH worksheet and set your worksheetcount

Now, you can select the sheet by an INDEX(worksheetcount).

Count the number of rows in the sheet, and set another variable (numberofrows).
Do another loop from 1 to the NUMBEROFROWS, and select the NUMBEROFROWS row. Copy the value.

Select the finalsheet, and find the next available row (use the same method as the prvious count row loop).
Paste the selection into the finalsheet.
Now select the worksheet that you were working on by the INDEX(worksheetcount).
Loop the second for statment.

Now loop the first FOR.

VIOLA. It is that simple. It can be done!