Get 3 cell values from all the excel files located in folder & make table in master file

Hello VBA Experts,
I have very limited understanding of VBA but i studied in uni about C, Java, php etc to tweak little bit of VBA.

My intention is, i got one folder full of Excel invoices generated by one Master Excel File. I added another sheet and this master sheet need to make table with three columns "
Date|Invoice No| Invoice Amount

My VBA code need to work on this sheet and fetches these three cell values from each excel sheet of different invoices.xls
I found one code on youtube, that works on something similar but my efforts are fruitless with partial success.
But looks like my limited knowledge will take forever to solve it, will appreciate if someone can help me on this, many thanks.

Ron

Please Login or Register  to view this content.

Do attention, you didn't do as provided in forum rules "Use code tags around code", please
edit your code, select code and click on '#' icon so text will be inserted in correct tag.

See attached file where I modified macro:

Please Login or Register  to view this content.

Regards,
Antonio

Antonio, my apology regarding code presentation, i fixed it ....
Right cell copied and pasted at right place, excellent work.. appreciate your time and efforts.

Two problems happens...little hiccups
1. All invoices gets reported but program ends with error please see first image for error code.. i guess it tries attempt on master sheet and ends when it couldnt find the "sheet1"
2. Invoice amount is formula and it copies formula not the value!!

Regards

Ron

Antonio, i placed MSGbox to show up file name each time loops through different invoice file while fetching three cells value.
it encounters Master file and deals it gracefully but next loops through " ~$Tax Invoice 14 April 12.xlsm" and crashes. with error msg window i pasted earlier for your attention.

Thanks

Regards

Originally Posted by antoka05

I modified my macro to fix formula problem but I don't know how to help you with error because i don't know where it crashes... I tested it and it works fine with my test files.

Regards,
Antonio