Consolidating several sheets data

[murp5972]

Hi, i've got a code that consolidates all of the data within several sheets in a workbook, and creates a new sheet titled "Combined" with all the compiled data. It also trims the data (removes a couple unnecessary columns and rows) and does a few other tweaks to make it look more presentable. However, as I must update this data every month, I've noticed an error with my new data not being properly combined. The error i'm seeing is this: All of sheet 1 is properly copied and pasted, except beginning at sheet 2, the FIRST row of data is not copied over and the LAST row of data is only copying a portion. This error seems to repeat throughout the rest of the sheets. Here's my code:

Please Login or Register  to view this content.

The attachment is made with dummy data that hopefully portrays the issue, however the code should not be ran on this attachment, it will not work properply, rather I'll explain the results of the code with my original file.

After the above code is ran, all of sheet 1 is copied properly into the new "Combined" sheet, but note starting at SHEET 2, COMPANY 23 is not included in the combined sheet, and yet it skips it and continues with COMPANY 24 then towards the bottom of SHEET 2, COMPANY 67 name is copied properly, but only ITEM REQUIRED 90 is pasted into the combined sheet. This error seems to replicate throughout.

Any help is greatly appreciated!! Thanks!

Mark excel tickler.xlsx

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[murp5972]

Thanks John, I tried your code but it resulted in the same issue.
My previous code here:

Please Login or Register  to view this content.

I've just made the following changes-



I just tried deleting:

Please Login or Register  to view this content.

and changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

It seems to have done the trick thus far, but i'm not entirely sure why. [/COLOR]

[Doc.AElstein]

Hi,

.. I was taking a look at this and struggling a bit without having actual data to work on.
.. and I see in the meantime JOHN H. DAVIS has come in
.. FWIW my suggestion was going to be along the lines of that :
. a ) it is dangerous to use the .CurrentRegion as this will give the “box” which only extends to catch / encompasses A1 and any cells “attached” to it. – So it will not catch any rows or columns where there is no linking cell, ( if that makes sense. )
. b) using .UsedRange is better – this will give the “box” which extends to catch any cell or row ever used ( except that it will not catch deleted rows or columns )

.

.. so b) is what you should be using.
.
. I think that ties up with what JOHN H. DAVIS has done ( Also he has tidied up a bit , knocking out unnecessary .Select bits and using (3) instead of (xlup) etc. ). He has a lot better idea than me so I am surprised his code is not working for you. ... maybe your (10) found solution is giving the Tip.. your code using a) was not catching the correct entire range for reasons i discussed, so using (10) increased it to the correct range. But that may not always work.
.
. Hope that may help point in the direction. Difficult as i said to check without actual data

Alan