VBA to copy-paste the last created Excel file inside of a folder and naming it.

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hello everyone,

    really ask for this help to finish this very long project, will be so grateful for any help please.

    I'm trying to write a VBA code that executed by a commandbutton, the code will search for a folder called "library" next to the excel file, and inside that library the code will search for the last created Excel file and copy it and paste it with the name the user gave it in the commandbutton.

    I've added 2 pictures that demonstrate what I'm trying to accomplish, and I've added a code not really related, the code is doing something different but I thought that it might help, the code copy paste an excel file located next to the file and paste it inside a folder, name it like the user type and create a row and create hyperlink . This code was created by JBeaucaire :

  • Thanks so much for the follow up Roy!!

    I've attached 2 files, with the unreverent code from above, it does something a bit different, it copies the excel's template file that located in the same folder with the excel with the button and paste it in a specific sub-folder,

    this might help because I'm trying to achieve something close to that, I'm trying to make the code to duplicate the last created excel file (macro available) inside a folder called "library" that located next to the excel's file with the commandbutton.

    I've attached the 2 files with explanation,

    Roy I appreciate it so much thank you!

    Please, if there's anything I can add/do, I'm 100% dedicated to accomplishing the task , I'll do whatever it takes.


    StrategyFromTemplate-Project Tracker.xlsm

  • What do mean by next to? Do you mean a sub folder of where the main workbook is?

    Try this, I haven't tested it but post back with any errors and I'll check back tomorrow.

  • Thanks for the big hopes here, respect your help here!

    I'm sorry by "next to" I've meant where the main workbook is.

    I've tried the CODE, I didn't quiet worked it didn't failed either it gives back message: "No files were found" ,

    I've tried to put the code inside "commandbutton" and inside a module, It gives the same error, I've tried to change xls to xlsx to see if it'll work, and tried to put in the Library folder xls file, Xlsx file and xlsm file , it didn't unfortunately

    I've forgot to mention that I'm trying to duplicate the last xlsm file I'm sorry it's was important detail I was missing to give.

    thanks a million! can't stretch it enough .


  • So the files are in the same folder. Maybe this will work

  • Thank you! really ! !!!

    I'm sorry for being not clear , the excel xlms files that I want to duplicate the newest one are located in a sub folder that refer to the excel file with the VBA button, the name of the sub folder is "Library"

    I'm getting right away to test the new code thanks!

  • Thanks for the follow up !

    I've tested it . Code Search For old "xls." file, I wish please if it can search for the latest xlms. It opens the Xls file that I've put there and VBA gives run error 1004 - doesnt had access to the file.

    I'll be playing with the code and see what I understand and can do , probably not much though.

  • still the same error, oh the other right * is for making it search whatever excel file - amazing I wish I knew how to write code like this, is it possible so that the VBA will ask the user what is the name of the new file so before it'll duplicate it , and add a row inside the table with a hyperlink ? that will be like.. a dream

  • You can use an InputBox to get the new name.

    I don't think we have the correct folder to search. It's currently searching the folder that the workbook with the code is in.

  • Thanks , I'll try to do it hope for a new breaking score in my VBA skills :)

    I added what you wrote in the first code

    "sPath = ThisWorkbook.Path & Application.PathSeparator & "library""

    to the second code , and now it search in Library , but it doesn't want to duplicate the file

    ThisWorkbook.SaveAs sPath & Application.PathSeparator & wbNew.Name & "- Copy", 51 ''///51 = xlsx

    looks like Japanese to me, maybe I can ask my girlfriend ^^

  • Is it finding the file?

    We can add an InputBox later.

    Try this so we can see what it is opening. When the code has finished see if it has opened the correct file.

  • Yes it finding the file and opening it on the background :)

    Thank you I'm running it and testing it for opening the newest file , doing it right away.

  • When you've checked that we can save it as the new workbook

    Oh it didn't saved the file, sorry for confusing, it gave error for trying to save it as a new file, but it opens the newest file on any folder I choose, but it seems to me like this was the trickiest part, not like I know how to proceed :)

  • This can be amazing for my project, thank you so much, I'll be able to make the user always open the last Excel file and work on it, giving ability to work with much needed previous texts inside. and just review and update what needed, it's amazing efficiency.

  • That's why I used the last *, it acts as a Wild card so any file extension beginning with .xls is found.

    This code will prompt the user for a name. Note, if they don't enter a name it will keep prompting twice more

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!