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

  • It finds the newest file and opens it and ask for a name for the new file, but the code doesn't want to duplicate it, the stubborn error is "run time error 1004 - didn't had access to the file-try to see that the folder exist /doesn't have something like [email protected]#$% .."

    And the VBA code flash in yellow this code

    wbNew.SaveAs sPath & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx 
  • Code finds successfully the latest excel file inside "library" folder, than the Code gives an error when trying to copy-paste the file

    wbNew.SaveAs sPath & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx

    The code opens the latest excel file I wonder if the code can open the new file that get duplicated instead , and add a link in the table below.

    Attached the excel file, I have in the same folder of this excel file a sub-folder called "library" and inside it few excel files and the code always finds the latest excel file successfully.

    StrategyFromTemplate-Project Tracker.xlsm

    Best wishes,


  • Maybe use SaveCopyAs, which saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

    wbNew.SaveCopyAs sPath & Application.PathSeparator & wbNew.Name & ans, 51 ''///51 = xlsx
  • Thank you for the follow up.

    It gives "compile error - wrong number of arguments or invalid property assignment" and flash yellow "Private Sub CommandButton1_Click()" and it seems like the code have problem with "SaveCopyAs".

    thanks ,


  • Hi Roy! :)

    Hovering the cursor doesn't gave any more details, I've attached a photo shows how it looks with yellow row "Private Sub CommandButton1_Click()"

    and a blue mark on "SaveCopyAs"

    regardless if I tried the "commandbutton" (code on sheet) or the button (code on module).

    sorry for all of it being a tricky problem sir.

    best regards,


  • Thanks for the follow up! Still same error like last time,

    Added the last updated excel file

    StrategyFromTemplate-Project Tracker.xlsm

  • I can't test it properly without creating dummy files and folder.

    Let me know if this works

  • Thank you so much Roy!

    The code successfully asks for a name for the new excel file, then locate the latest excel file inside the folder and copy- paste it, giving it the name which the user has chosen.

    Is it possible please to add to it that the new excel file will be open and the code will make a new row in the table, with a hyperlink to the file ?

    I was trying to integrate this to do it but I'm not really good at VBA and it failed

    With ThisWorkbook.Sheets("Strategies Contents").Range("B" & Rows.Count).End(xlUp).Offset(1)
        .Value = StrategyName
        ActiveSheet.Hyperlinks.Add _
            Anchor:=.Offset(, -1), _
            Address:=NewName, _
        Workbooks.Open NewName
  • Quote

    code will make a new row in the table, with a hyperlink to the file

    Do you mean the new file? Why would you want to hyperlink to the ame file/

  • Wow that's an interesting question to speak about, because I've found that , at least in my opinion , this holds an unbelievable efficiency and productivity power that goes long long way afterwards,

    For me excel is the real power house for database management , I don't use Access, that because I just have a table that connect with hyperlinks to different excel files and just draws the important data from the other excel files to the table(and if I change something I update)- while keeping my main excel file with the organizing table running like a rabbit with all the power of excel instead of the unfriendly Access, for me its a perfect win win situation , If you like I can show you my work with deeper insights on Zoom .

    Here, it's a special database that focus always on the last file, because there's an ongoing update action that takes place on the newest file all the time.

    I hope that I didn't explained wrong ,please I've added pictures to illustrate what I'm asking ,

    Thank you for being the only one who helped me in this problem

  • I did the last part of the code, I don't know how, but it works, the code add a new row in the table, add the hyperlink to the new excel file and opens the new excel files,

    Is it possible to make the code copy-paste the last Created excel file in "library" folder, instead of last Modified excel file ? I just paid attention to it and it will make trouble down the line because the real intent is for the last created file.

    Is it possible in a case of an error when the code search for the last edited/created excel file , that it will open simply the file "Template.xlsm" please ? (just to make sure that the code will still work even if there'll be an error, sometimes I move files from one hard disc to the other and the all files get the same date created/edited)

    best regards,


    last code working, with my add on .

  • Quote

    It should be saving into the folder called library.

    yes , it is , the code working good just need 2 adjustments.


    Do you ant to open a template workbook not the last modified?

    Right now it open the last modified, but I want it to open the last created. (and also copy paste it and everything evolved this code on last created instead of last modified)

    And only in a case of error, I'm thinking that sometimes I move files from hard disk to hard disk and it doesn't save the last created date on the excel files, so I'm thinking maybe in this case the code will give an error, so I wonder what you think and if it will be smart to add "if" to the code that in a case that the code doesn't find last created date excel file that it'll open specific excel file in the "library" called "template.xlms" ?

    thank you for your patient brother.

    best regards,


Participate now!

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