Find file in specific folder+renaming it automatically after cell update in other workbook

I am using as database a file "cargoes 2016" sorted by "reference number" which creates/saves automatically a file "calc sheet" in another folder naming it correctly.

I would like that as soon as the cell "AI"(=date) of "cargoes 2016" is updated, the VBA code:

- goes to the folder of calc sheet
- find the relevant calc sheet title, the searching criteria the "reference number"
- rename the relevant calc sheet title adding at the end the "date"

here is the code I have until now (input under "cargoes 2016")

Please Login or Register  to view this content.

the table "cargoes 2016" indeed use a userform to enter/view data (here attached the file).

Each time a new entry is create via this userform, it creates/name automatically a file "calc sheet" saved in another location and based on a template. The new calc sheet is renamed as per info entered based on a specific title format (all this works perfectly).

what I would like is that each time I update the column "AI" of the "recap"sheet of cargoes 2016, the code goes to the folder of the calc sheet, find in the titles the one of "cargoes 2016/recap/column c" and automatically (without opening it) rename the calc sheet adding at the end of the title "cargoes 2016/recap/column c/column "AI".value

For example, as per attachment, if in "cargoes 2016" I insert in the column "AI" for the ref 2016CT007 "21-12-15", The code should go in the specific location, find the calc sheet whose title includes "2016CT007" (HERE ATTACHED) and renamed it adding at the end of the title before the extention "-" & "21-12-15" (i.e. cargoes 2016/recap/column c/column "AI".value) without opening the file

I amended the code so that after inserting via the userform of cargoes 2016 a "BL DATE" (=CELLS "AI"), and clicking save of the userform (i.e. to update the related cell), the code goes to the specific location, find the file whose name includes the "REF.value", and rename it....

Please Login or Register  to view this content.

However I get an error "file not found", when putting the cursor on "oldname" and "newname" all looks ok......can someone help on this case?