Excel Combine worksheets into one Closed

Question

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:

ac3mark · Answer

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!

Excel Combine worksheets into one

1 response

Similar discussions

Subscribe To Our Newsletter!