Hi Claus,
I still have no luck getting it to work.
Is there a way you could modify this one to copy and save as values and number formats in C:\Users\Ditchy\Desktop\. ?
This macro works but copies to desktop & does not save as values and number formats



Sub WorkbookSaveCopyAs2()
'use the Workbook.SaveCopyAs Method to save a copy of ThisWorkbook which your are working in, with a unique name everytime:


Dim fname As String, extn As String, MyStr As String
Dim i As Integer, lastDot As Integer

'change the current directory to the ThisWorkbook directory:
ChDir ThisWorkbook.Path

'find position of last dot, to distinguish file extension:
For i = 1 To Len(ThisWorkbook.Name)
If Mid(ThisWorkbook.Name, i, 1) = "." Then
lastDot = i
End If
Next i

'extract file extension and dot before extension:
extn = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - lastDot + 1)
'extract workbook name excluding its name extension and dot before extension:
MyStr = Left(ThisWorkbook.Name, lastDot - 1)

'specify name for the copy - the time part in the file name will help in indentifying the last backup, besides making the name unique:
fname = MyStr & "__S_Ditchfield__" & Format(Now(), "dd-mm-yyyy ---- hh-mm AMPM") & extn


'save a copy of ThisWorkbook which your are working in, specifying a file name - use this method to save your existing work, while your current workbook remains the active workbook:
ThisWorkbook.SaveCopyAs fname



'your current workbook remains the active workbook, the saved copy remains closed:
MsgBox ActiveWorkbook.Name

End Sub


very much appreciated
regards
Ditchy

On Monday, May 12, 2014 5:29:43 PM UTC+10, wrote:
Hello,

I am trying to copy a sheet, saving as a new workbook, values & number formats

also with the filename taken from (A1) all with the click of a button, sounds easy!

here is the macro



Dim myFileName As String

With ActiveWorkbook

* *worksheets(1).Copy 'to a new workbook

* with active sheet with.UsedRange.

Copy.PasteSpecial Paste:=xlPasteValues 'remove formulas??? *

'pick up the name from some cells???

*myfilename = .range("a1").value & ".xls" myfilename = "C:\Users\Ditchy\Desktop\" & myfilename *'????

.Parent.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

.Parent.Close savechanges:=False

End With

End Sub



any and all help appreciated



regards

Ditchy

Ballarat

Australia