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

  • I've tested the whole code, it works pretty good unless the user choose a name with "." , then it creates a file with no excel extention ".xlms". (like: new name 2.4.21 - that's it without .xlms)

    Is it possible to change the code that instead of asking the user for a name, the code will choose a name automatically , and the name will be "new name"+Today date ? that will be seriously awesome.

    for example executing the code today will give a name of:

    New Name 3.5.21

    and tomorrow will be

    New Name 4.5.21

    and so on .

    just wondering if that possible with fixing the problem that the code doesn't give excel extension when a "." involved in the name.

    it makes the whole code bitter sweet .

    thank you already for all the help .


  • Try this. It removes "." if the user adds one when entering the name. Then adds the data to the end of the file name, which is always a good idea when duplicating workbooks.

  • Thanks Roy , it was a journey , I really made new insights, understanding and skills in VBA .

    sorry for the delay, my laptop fell and I got short circuit in one of the mother board component, this is my older laptop that I just furnished .

    My last piece in the puzzle is making this exact code work for a folder, that means instead of finding the last edited/created file in a folder the code will find the last edited/created folder in a folder - and then copy paste it .

    Is it possible as a finisher bro ?
    I'm on it, really surprised myself with VBA in the last week, but I think I need one last help in this one.

    best regards,


  • I just wandered 4 hours on the internet and realized that finding the last modified/created folder in a specific directory is extremely tough VBA challenge ;(;(

    Hope I'll keep my hair after this last challenge of death :/

  • I was thinking on a second possible solution that will be also amazing -that instead of finding the last edited/created folder inside a folder and copy-paste it , there will be a systematically naming of the new folder instead of asking the user for a name , like this:

    ans = "new folder" & " - " & Format(Date, "dd-mmm-yyyy")

    so the code could search for the folder with the highest value- the latest number if that possible (because I need the folder with the most recent date )- so the newest folder always will get picked and the code will copy -paste the last one. which I have no idea how to do but I was keep thinking on it for the last who knows how many hours.

    Thanks ! Thanks ! THANKS!!!

    Be Blessed.

  • Here's a UDF to get the name of the newest subfolder.

  • Do you mean like this?

  • That looks so awesome that I'll give myself a little sleep now, so I'll wake up fresh to work on it , thank you so much Roy! never I was learning so much VBA in what feels to me like a very high level , and you just swim like a shark in it.

    much appreciation for a giant help .

  • Thanks Roy!

    Pls, how can I sign this beauty to give value ?

    I was trying

    sub FunctionGiveValue ()
    dim GetNewestFolderValue as string
    GetNewestFolderValue = GetLastFolder ("ThisWorkbook.Path & Application.PathSeparator & "library"")     ' failed
    end sub

    I was trying to use it with dynamic path instead of static one ("C:\folder") which unfortunately failed

    thanks for all,


Participate now!

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