Hi Garry,
I have managed to get this to work, but only with an unprotected sheet, when protected it comes up with a error (400) and saves it to the desktop with the name (book 6)not the workbook name. Is there a work around?
much appreciated
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