With the changes from your first reply, when I do a save as and go to the
folder to check, the workbook still does not exist? I changed SaveName to
Variant as well as NewName to Variant, would that cause the problem? Thanks
again for you assistance.

"Dave Peterson" wrote:

You can copy a worksheet to a new workbook and save that workbook. Is that what
you meant?

Something like this may get you going:

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = ActiveWorkbook.Worksheets("sheet1")

wks.Copy 'to a new workbook
With ActiveSheet.Parent
.SaveAs Filename:="hithere"
.Close savechanges:=False
End With

End Sub

If you're going to overwrite an existing file, put:

application.displayalerts = false
..saveas filename:=....
application.displayalerts = true

to suppress any "are you sure" prompt.


D.Parker wrote:

Dave:

Is there a way to save a particular worksheet into a new workbook?
Otherwise, I will just have to password protect the code in the current
workbook. Thanks again!

"Dave Peterson" wrote:

This line:

SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

only returns the name of the file the user chose--it doesn't do the actual save.

Sub RenameFilenameUponClose()

Dim SaveName As variant '<--changed
Dim fFilter As String
Dim NewName As String

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

if savename = false then
'use cancelled--what to do?
else
thisworkbook.saveas filename:=savename, fileformat:=xlworkbooknormal
end if

End Sub

I change SaveName from a String to Variant--so that it could represent the
boolean value False, too.

D.Parker wrote:

I am trying to save a worksheet as a separate workbook. I am manipulating
code from a pervious response. The Save As form comes up and I can set a
file name and choose a path, but when I click the save button there is no
file in my designated folder. The Save As is good since the user will be
changing the filename each time upon exiting. I'm assuming I missing some
code somewhere? Secondly, is there a way to save the worksheet object as
opposed to the entire workbook (i.e. save the worksheet into a new workbook,
less the VBA code preferrably)?

Sub RenameFilenameUponClose()

Dim SaveName As String
Dim fFilter As String
Dim NewName As String

NewName = "P2 LogHistory Shift"
fFilter = "Excel Files (*.xls), *.xls"
SaveName = Application.GetSaveAsFilename _
(NewName, fileFilter:=fFilter)

End Sub

Your help is greatly appreciated as always.

--

Dave Peterson


--

Dave Peterson