VBA to combine same worksheet in different workbooks into one without the heading

Hi,

I need help to combine worksheet named "Metal List" from different Workbooks who have many worksheets and the worksheet name "Metal List",into one sheet to get a consolidated data and I want it in a new workbook. I want the 1st row of all the merging sheets to be ignored as they contain the headings.

I am using the below code but its not being very useful as it seems to copy the data of the first sheet twice!! and does not ignore the first row so there are multiple heading in the sheet.

{If possible can it also automatically combine the worksheet "metal List" of workbooks saved in that particular folder or drive?}

Please Login or Register  to view this content.

Many Thanks for your help

Make a new workbook and Put some headings in the First row.
Here is the code:-

HTML Code: 

Try these links..

http://answers.microsoft.com/en-us/w...llReplies#tabs

http://www.mrexcel.com/forum/excel-q...encrypted.html

One sec.

You didn't suffixed "\"

Try this code:-

HTML Code: 

Thank a Million it works!!!!!.... I do have one question.. will it automatically merge sheets from that folder when more workbooks are added or will I have to run the macros every time??? can it be done automatically?? because every time I run it...it copies from the beginning so if I have already copied data from two workbook and run it later...it copies the data from the new worksheet and also those two workbooks again

No, you have to re run the macro when you add workbook to the Folder.

Use the Revised Code Below.
Now it will ask you the file using Open File dialog box. Give any file in the New Folder having new files in it.
It will add at the end of the Existing sheet.

HTML Code: 

Alf is correct..
You need to clear all the cells on the destination sheet. Just select all the cells and click "Clear All" button in the Editing Section of Home TAB.

and Then re run macros.

Using what Alf tells you.

It will definitely do the job.

Can you provide some files to test with..?

make a Zip file containing a Main file and some Extract file. and Upload.

May be there is something wrong with your input sheets..!

Okay here is the amended code.
Change the folder in the fPath variable and run the code.

HTML Code: 

Test and Tell.

Okay then Remove the Error Handler lines from the code.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and Run the code. Press Debug when Error comes.
and Take a snapshot of the screen, Highlighting the Yellow line.

Oh..!

Did you changed the

Please Login or Register  to view this content.

and
Sheet Name in the following line

Please Login or Register  to view this content.

Hi Vikas,

Thank you for your response!!

Please find attached the screen shot

Error.jpg

One more question..

Do your excel files contain Formulas ..?
I am talking about the Input files which are to merged.

OR

Can you provide me two input files to test with...?
I won't take time then..

Make a dummy Excel file and upload..!

The Problem I got in your Input file was that that they were Protected with a password, hence not letting the code to work properly.
So I make two unprotected copies of sheets to carry on with my test. However I have changed my code to unprotect sheet before moving on to copying data.
Change the Pwd Variable holding password for your input files. I am Assuming there is only one password for all your files.

Now come on to the Dropdown references your were talking about. I thing the best way to disregard them would be to move them to a new sheet ( say Drop Downs Sheet ). This is more appropriate as it won't be easy for me to capture only the data area as you said that there can be many empty rows as well.

If you take care of all of above. The code will satisfy your needs very well.
Here is the revised code. I am assuming there are 4 Header rows in all your Input files.

HTML Code: 

Test and tell. Don't forget to change the fPath variable and Pwd variable accordingly.

Okay Varmoh..
I think this project is counting its breath.
I had to use an extra PwdBreaker code to unprotect sheet. Here is the Code:-

HTML Code: 

Post both of them alike in your VBA window and run the MergeIntoOneSheet Macro.
Hopefully it would do the job as necessary.

Thanks for this kind response ..! Varmoh

Good luck.!