Merge data from many many tabs (worksheets) into a new sheet

[dmellor2]

Hi All,

I need help from anyone who has done this before or can easily help me....

Basically I inherited a Excel Workbook which has 128 different tabs, unsurprising this is almost impossible to manage.

My requirement is to merge all of the 128 tabs into 1 tab 'Sheet1'.
The macro should keep going even if there are blanks, it should move onto the next tab only when there are over 10 consecutive blanks in Column B.

I have tried searching the forum but nothing seems to cater for the specifics of this case. I would really appreciate some help. I mean really, i am in a mad panic thinking i will have to do 128 tabs manually otherwise.

Thanks, Darren

 

[Wakina0629]

Quick question..

What if my data isn't from the whole worksheet but certain range only (say, from A1: Y80, A82:Y100....), what should I change on the code above? Thanks.

 

[tstav]

Well Wakina0629,
if there's no way of automatically pinpointing neither the start nor the end of data, you do it the hard way. You type it in...
You'll have to change the following piece of code for each of your Worksheets

'This is the Range that will be copied to the new Worksheet
   With ActiveWorkbook.Worksheets(intI)
      Set rngRange = .Range(.Cells(1, 1), _
         .Cells.SpecialCells(xlCellTypeLastCell))
   End With

with the following

'This is the Range that will be copied to the new Worksheet
Set rngRange = ActiveWorkbook.Worksheets("NameOfSheet").Range("A2:Y80")

Welcome to the forum...

 

[CBWH1985]

This is PERFECT! Just what I needed ... Thanks Mr. Excel

 

[salman426]

Re: Merge data from many many tabs with diffrent stucture (worksheets) into a new sheet

Thanks for the code


dear sir,
i required the code for merge data from different sheets with different structure but in that i need 10 columns which are the same column name.

i this you understand what am explaining

waiting for your replay

Thanks & Regards
salman