Extract multi excel file into a single file

[saputroa272]

hello, I am trying to extract many profiles & product files into single file.
on sheet "Merchant" had succeeded but for sheet "Produk" has not been successful.
here it is my code

Please Login or Register  to view this content.

any help would be appreciated
and here is my files

[Doc.AElstein]

Hi saputroa272

hello, I am trying to extract many profiles & product files into single file.
on sheet "Merchant" had succeeded but for sheet "Produk" has not been successful.
here it is my code………
and here is my files……

. You need to give us much more information in order for us to help you. It is good that you have given us the code – that is a good start point for us. And you have given us the “Before File” Extract Multifile trial.xlsm . But from the information that you have given we can have no idea what is going wrong as you have not stated clearly exactly how you want the File “Extract Multifile trial.xlsm” to look like after successful running of a code.
. Please in future provide a second “Extract Multifile trial File” ,( the “After” File ) which you fill in by hand to show exactly what output you want finally after a successful running of a code
……..
. In any case I have made a few guesses as to what you want and rewritten your code so that it consolidates profiles & product files into the File “Extract Multifile trial.xlsm” assuming that “Extract Multifile trial.xlsm” would be empty to begin with:--***
. - I think your main problem in your original code was in determining your Nextfree2 -
. a) ***File “Extract Multifile trial.xlsm” appeared to have some data in a long way down in sheet Produk that you may have overlooked
. b) even when deleting this data your calculation of NextFree2 seemed to give erratic results which I do not yet understand. I had to empty cells which looked empty to get the codes too work. But I am not sure if you intended having this data there?***
. But I have also simplified greatly your 'copy, paste, whatever stuff” as well. ( Assuming for now you have no empty rows within the data to be consolidated as per your sample data )

Code:

Please Login or Register  to view this content.

Alan Elston
Bavaria
Germany

[saputroa272]

well thanks in advance for the reply doc, it seem your code much shorter than mine I will learn how it works to get my code more efficient. the idea is to copy non blank value (products list)in profile&product workbook sheet"produk" range C3:M200 to extractmultifile workbook sheet"produk" at the first blank cell in B3:B column.It embarasing that i found my self forgotten to write ".value" on sheet"produk" so here is the worked code:

Please Login or Register  to view this content.

so it Solved. sorry for my english

[Doc.AElstein]

Hi saputroa272
. Just a quick follow up here as I was working on a similar problem and had horrendous problems in checking for “Empty cells”……

. 1) Usually .Value would be taken as default if omitted. But interestingly here it leads to your code not working, that is to say NextFree2 seemed to give erratic results which does not appear to be the case with your modified code.
. A strange problem, but emphasizes as always not too rely on the default and always remember to include .Value….

. 2) There could still be some problems lurking to catch you out unexpectedly to do with “Empty cells”..
.. here is another code alternative ( 2 versions ) which my be more appropriate, or at least another alternative to have as a back- up..


Full code with explaining comments:

Please Login or Register  to view this content.

Shortened / Simplified code:

Please Login or Register  to view this content.

Alan

[saputroa272]

Hi Doc.AElstein
1. noted sir
2. superb, both of them are worked fine. Its good for me as a back- up if i find a problem with “Empty cells”
thanks a alot

[Doc.AElstein]

Hi Doc.AElstein
1. noted sir
2. superb, both of them are worked fine. Its good for me as a back- up if i find a problem with “Empty cells”
thanks a alot

. You're welcome,
. Thanks for the feedback.
. Alan Elston