Excel - Saving
 

Hi,

Please can someone help???

Its been a while, but after using this script where a sheet is copied
and saved in the root directory of the original file, how can it be
changed to create a popup "save as" screen so the user can choose??



Sub SaveMe()

Dim myFileName As String
Dim newWks As Worksheet
Dim CurWkbk As Workbook

Set CurWkbk = ActiveWorkbook

myFileName = CurWkbk.Worksheets("Mail").Range("p17").Value _
& "_" & Format(Now, "dd-mm-yy") & ".xls"

CurWkbk.Worksheets("EAR").Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks.UsedRange
'convert to values
.Value = .Value
End With

newWks.PrintOut preview:=True 'for testing!
newWks.Parent.SaveAs Filename:=myFileName,
FileFormat:=xlWorkbookNormal

'either
CurWkbk.Close savechanges:=False 'true
'or
newWks.Parent.Close savechanges:=False

End Sub


Regards

Andrew


--
ajw150
------------------------------------------------------------------------
ajw150's Profile: http://www.excelforum.com/member.php...fo&userid=8020
View this thread: http://www.excelforum.com/showthread...hreadid=229515