Merge Sheets into One

[Ammarbokhari]

Hi,
I have a lot of sheets in one workbook (about 100 sheets), is there any way by which I can merge them to make one sheet?
the data on the sheets is mostly in the form of tables but there are some empty rows as well (but there are not more than 10 empty rows consecutively within the data, so if more than 10 empty rows are found in search, then the search can be stopped and shifted to next sheet)
also i want the values to be pasted to the output sheet, instead of function or formulas as the links are broken once the data is shifted to other location.
any help in this regard is highly appreciated.

 

[SirSchiz]

Hi,

I'm new to the message board, but I cam across this post. I have struggled with this for a long time. If you're using a 32 bit version of Excel say 2007 and later. You can look for an amazing add on called ASAP Utilities. I'm by no means selling a product here, just offering a solution. It's free for personal use and has really helped me solve a lot of the issues that Microsoft left out.

Hope this helps! Good Luck.

 

[Ammarbokhari]

@ SirSchiz,
This works perfectly. Exactly what I was looking for
Thank you for making it so easy
Its great that you came across this post on the message board.
I have been trying to find this type of macro for a while and always there was something missing, this one works perfectly and I have the option of choosing the range manually which is best.
Thank You again!