Merge many sheets into one, re-order cols, include sheet name on merged sheet rows

Hi, im a new memeber but long time troll. Learned lots here, appreciate everyones help here!

I have a huge project, 5 yrs of workbooks needs merged onto 1 worksheet.
Each workbook contains indiv state-named worksheets.
Want to merge all the sheets on 1 master per workbook, i'll worry about merging the workbooks later...

Found some cool code to merge the sheets but needs tweaked some.
I write ASP.NET (vb) and very handy with excel 2007 complex formulas etc but vba is not my strong suit...

tweaks to this merge code i need are:

1 - exclude hidden worksheets when merging
2 - exclude blank rows when merging
3 - can the columns be re-ordered (heading names match on the indiv sheets, but may not be in same order)
4 - add the source sheet name to each row Col A on the merged sheet.

I'm uploading a sample with the merge code included module, any help would be SOO appreciated!

Thanks so much, i will give this a try and see how it works out!

Thanks so much for submitting your sample codes (nice webpage you have, i will have to explore when i have time)

i made a little time to test your combining worksheets code today. the column re-ordering is GREAT. nice function there, i will definately find use for that! and the sheet names are included in col A as i hoped for, fantastic...

is there a way to NOT pull hidden worksheets, to only combine the visible ones?

the code sample i originally included in my sample file attachment finds the last used row on each worksheet and lets user set which row is the header row, but i don't see that in your code (not yet anyway have not had alot of time to go thru it yet). i can try to add those features into yours, not sure how successful i will be..

Please Login or Register  to view this content.

I wonder if this LastRow code is finding like last cell on a worksheet (which maybe due to formats etc), as opposed to last USED row with data populated in the cells on the row, you understand what i mean?

Yes that will help alot, thanks!