Merging All Spreadsheet within a Folder

Hi,

I am new at macro, In fact never tried writing any but would like to try, Never written anything in VB but know little programming.

I would like to Merge several Spreadsheet together from a folder, and the number of the spreadsheet within the folder is not specified as it varies depending on day. I am just wondering if this is possible. I have tried using the normal record function but they merge the spreadsheet but I dont know how to get it to look for other files in a folder. Thanks.


Regards,
Jeffrey

Since you haven't specified the pattern of your file name, this code will merge all .xls files into one workbook.

Please Login or Register  to view this content.

Thank,

Can you by any chance let me know how to insert this code to macro. I have tried but to no avail and obviously I have done something wrong. As mentioned, I have never written any.

Got it working, Just some foolish mistake. Thanks. However, instead of creating a next sheet in the same file, can two sheet be combined to one. As in, the first file at the top and the second file at the bottom ? Really appreciate your help.

Regards,
Jeffrey

try

Please Login or Register  to view this content.

Sorry to be a pain, Have not tried the second code yet but since saving the first code to the macro. Everytime i open a spreadsheet, the file "personal" Pop up. Thanks

Originally Posted by mengghee

Sorry to be a pain, Have not tried the second code yet but since saving the first code to the macro. Everytime i open a spreadsheet, the file "personal" Pop up. Thanks

Nothing to do with my code.

Not sure if you have the time and dont wanna waste any of your time, but i really want to learn the coding instead of just copying the code. Can you by any chance insert a little bit of comment. Honestly, dont bother if you dont wanna do it. Not too sure if ppl in this forum does that :P as haven't been here long enough. Thanks a lot

p/s: the second code works great !

Thanks, All my spreadsheet contains 3 last useless row which needs to be removed. Just wondering how I can remove the last 3 rows of every spreadsheet merged. Tried altering code myself but with error.

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks for the code, However, there are two alteration still required and wondering if you can help. First, the code inserted an empty row on the spreadhsheet that hopefully can be removed. Second, the code copies the entire second and consecutive sheet's header (first row of every sheet). Since we only need the first header, can this be removed ? Thanks

Regards,
Jeffrey

Also, I have a quick question, in the code it says

do while file name is returned by dir function


but the directory will always have file. doesn't it then keep looping ? I am obviously wrong but just wonder how that works. Thanks