Problem when workbook is not save in desire sheet.

[jp16]

Hi, i am given a task to Combine several excel files from 1 folder to a single sheet. All these excel file have a sheet call AAA.
Hence i go online to search for code and amend myself. It work well, however, when these excel file is not save in Sheet AAA, an error occur. I have no idea how to make it open the file then go to sheet AAA. I think something is wrong with the Bold part coz it is highlighted when i run the macro.

Please Login or Register  to view this content.

Anyway, the whole code look like this. (please advice how i can be improve)

Please Login or Register  to view this content.

Basically, to extract all the sheet AAA data in the folder and copy and paste in sheet BBB of another workbook.

[schulzy175]

when these excel file is not save in Sheet AAA, an error occur

Can you elaborate this a little more?

[bakerman2]

How about this ?

Please Login or Register  to view this content.

[jp16]

I mean when user didnt save the file at tab AAA. Hence when u open the file, its is not at tab AAA. I tried my code and it work ok provided all the file is save at tab AAA. but when one of the file is not, then error occur

[jp16]

How about this ?

Please Login or Register  to view this content.

hi, how about if i wan all the 1st sheet? How shd the code be change?

[bakerman2]

Please Login or Register  to view this content.

[bakerman2]

Thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jp16]

Anyone can help to make my Path Link to a cell? I mean user doesnt have to open VB in order to change the path. Lets say A1, how to make my code read A1 instead of manually open VB n type in?

[bakerman2]

Please Login or Register  to view this content.

Make sure you always type \ at the end of the path. Or would you rather like a built in check to see if they have done that ?

[jp16]

Yes, if possible. Actually, they will import quite many file. I am thinking of instead of pasting to BBB, it create a NEW tab and paste it there instead of BBB. Is it very hard to amend to make it create a sheet and paste it at new sheet?