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.
Test and tell. Don't forget to change the fPath variable and Pwd variable accordingly.HTML Code:
Bookmarks