Change...

myFileName = "C:\Documents and Settings\All Users\Desktop\ & myFileName"
to
myFileName = "C:\Documents and Settings\All Users\Desktop\" & myFileName

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"George" wrote:

I have the following code that saves one sheet to a folder. What I`m after is
to save the sheet so that it can not be altered and rename the tap name to
the contents of cell "E1". Also I would like to keep adding the same sheet to
the same folder but with different Tap name.........ie 2006, 2007, 2008 so on.
Here is the code I have so far:

Private Sub CommandButton1_Click()
Dim myFileName As String
With ActiveWorkbook
Worksheets("Sheet2").Copy 'to new workbook
With ActiveSheet
With .UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues 'remove formulas???
End With
'pick up the name from some cells???
myFileName = .Range("e1").Value & ".xls"
myFileName = "C:\Documents and Settings\All Users\Desktop\ & myFileName"
.Parent.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
.Parent.Close savechanges:=False
End With
End With
End Sub



--
George