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 have done this but only make this workbook rename and save. I want to make a copy and save to desktop, any suggestions?

 Dim newFile As String
Dim dYear As Integer
dYear = Year(Date)
newFile = "Agent Stats -" & dYear
MsgBox prompt:="Your new file will be named " & newFile & ".xls"
ThisWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & _
    "\Desktop\" & ThisWorkbook.Name & "_copy", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.SaveAs Filename:=newFile

 

[AlexaS]

You shouldn't need to create a copy and then save. You should be able to do "ActiveWorkbook.SaveCopyAs FileName:=" and then whatever your file extension is. As far as saving to the desktop there are some suggestions here that might help you.

 

[MARK858]

Save the workbook first with the original filename and then do the saveas with the new name?

 

[Adrac]

i need to be able to create a copy as this workbook deletes all data in it to start a new year. so what i want is to copy the full one change name to year name then clear all data but keeping the same name as the master has. and thanks alexas i have added it to the macro but doesnt work with SaveCopyAs

This is what i added

ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & _
    "\Desktop\" & ThisWorkbook.Name & "_copy", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled

This is the full macro

Sub Delete_Data()
Dim varResponse As Variant
     
    varResponse = MsgBox("Are you sure you want to completly delete data in all month tabs? ", vbYesNo, "Selection")
    If varResponse <> vbYes Then Exit Sub
    
    
Dim newFile As String
Dim dYear As Integer
dYear = Year(Date)
newFile = "Agent Stats -" & dYear
MsgBox prompt:="Your new file will be named " & newFile & ".xls"
ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & _
    "\Desktop\" & ThisWorkbook.Name & "_copy", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.SaveCopyAs Filename:=newFile
'Delete all data in sheets
Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")).Select
Sheets("Jan").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("a1").Select
Sheets("Front Sheet").Activate
Range("A1").Select
End Sub

but the savecopyas is creating a error

 

[AlexaS]

but the savecopyas is creating a error

Ok. What error?

 

[Adrac]

Ok. What error?

Its erroring at the file format part. Not sure what exactly what it was as not at work now.

 

[AlexaS]

Its erroring at the file format part. Not sure what exactly what it was as not at work now.

Ok. In order to help we need to know the exact error. So when you get back to work can you check it again? Although why are you saying in your message box that it's going to be an .xls but you are formatting as a .xlsm?

 

[Adrac]

Ok. In order to help we need to know the exact error. So when you get back to work can you check it again? Although why are you saying in your message box that it's going to be an .xls but you are formatting as a .xlsm?

Ok so im back at work now and it hilighting 'FileFormat' and error: "Compile error: Named argument not found"

i changed the message to .xlsm.

 

[AlexaS]

What is the current file format? Apparently (as found here) SaveCopyAs can only save the same format as the original file. I learned something new

If that is the case then the code here should be able to help you (with a little tweaking).