Quicky

[Adrac]

Hello gang,

Im trying to create a copy of my document and then change the name (without affecting the master copy) and save into desktop regardsless of username, as part of a macro. Can you suggest a workaround?

Adrac

 

[Adrac]

ok i dont mind if the file is the same format but without "FileFormat:=xlOpenXMLWorkbookMacroEnabled" this line it will not work. The master spreadsheet is .xlsm and im guessing the fileformat is right?

 

[AlexaS]

The file format is the same, so that is not the problem. Can you try just having:

ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & _
"\Desktop\" & ThisWorkbook.Name & "_copy" [COLOR=#ff0000]& .xlsm[/COLOR]

instead of having the "File Format" part in there?

 

[Adrac]

almost there lol i have the .xlsm highlighted and the error "Compile error: Invalid or unqualified reference"

 

[AlexaS]

ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & _
"\Desktop\" & ThisWorkbook.Name & "_copy[COLOR=#ff0000].xlsm"[/COLOR]

Try that.

 

[Adrac]

awesome yes with a bit of tweeking i have got it working, thank you so much

One more thing i need to include in it the year date in the title. but for last year. so at year end when i want the macro to run i need it to have "filename_yyyy" for the year before.

so & ThisWorkbook.Name & _yyyy<YYYY>.xlsm

 

[Adrac]

sorry i was being a noob, i got it now

ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & _
"\Desktop\" & ThisWorkbook.Name & "-" & Year(Date) & ".xlsm"

Thanks again alex, your a star, im sure ill be posting my next project lol

Adrac

 

[AlexaS]

Glad you got it working