Use a Macro to Save a Copy of a Workbook?
 

Hi,

I am trying to streamline my department's scheduling process by using a
macro. I want an employee to open a master workbook (master.xls), then
enter data, then SaveAs a workbook with a name that is taken from a
string in cell A2.

I want the last of those three steps to be done automatically using a
macro. I am new to programming excel and my current attempts at this
macro are a mess of copy-and-pasted code from these message boards.

My current code works to some extent. It does what I want it to, but
Excel crashes immeadiately after. Can Someone please help me out with
some clear code to prevent Excel from crashing after the save? I would
also like if Excel did not ask to overwrite a file with the same
name... I want it to automatically overwrite without complaining.

Thanks,
Joe

Use a Macro to Save a Copy of a Workbook?
 

Joe, try this,

Sub Save_As()
Dim SaveName As String
On Error GoTo E
Application.DisplayAlerts = False
SaveName = Range("A2").Value
ActiveWorkbook.SaveAs Filename:=(SaveName), FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
E:
Application.DisplayAlerts = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Joe D" wrote in message
oups.com...
Hi,

I am trying to streamline my department's scheduling process by using a
macro. I want an employee to open a master workbook (master.xls), then
enter data, then SaveAs a workbook with a name that is taken from a
string in cell A2.

I want the last of those three steps to be done automatically using a
macro. I am new to programming excel and my current attempts at this
macro are a mess of copy-and-pasted code from these message boards.

My current code works to some extent. It does what I want it to, but
Excel crashes immeadiately after. Can Someone please help me out with
some clear code to prevent Excel from crashing after the save? I would
also like if Excel did not ask to overwrite a file with the same
name... I want it to automatically overwrite without complaining.

Thanks,
Joe

Use a Macro to Save a Copy of a Workbook?
 

If you know the folder name where the file will be save you could do it
something like this:

Sub SaveFile()
Dim Pth As String
Dim flName As String
Pth = "C:\Temp Folder\"
flName = Sheets("Sheet1").Range("A2").Value
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Pth & flName
Application.DisplayAlerts = True
End Sub

Or you could prompt the user to select a folder using code like this (not
this will also allow the user to change the filename):

Sub SaveFile2()
Dim flName As String
Dim fullName As Variant
Dim filter As String
flName = Sheets("Sheet1").Range("A2").Value
filter = "Excel Files (*.xls), *.xls"
fullName = Application.GetSaveAsFilename(flName, filter)
If fullName < False Then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs fullName
Application.DisplayAlerts = True
End If
End Sub

Hope this helps
Rowan

"Joe D" wrote:

Hi,

I am trying to streamline my department's scheduling process by using a
macro. I want an employee to open a master workbook (master.xls), then
enter data, then SaveAs a workbook with a name that is taken from a
string in cell A2.

I want the last of those three steps to be done automatically using a
macro. I am new to programming excel and my current attempts at this
macro are a mess of copy-and-pasted code from these message boards.

My current code works to some extent. It does what I want it to, but
Excel crashes immeadiately after. Can Someone please help me out with
some clear code to prevent Excel from crashing after the save? I would
also like if Excel did not ask to overwrite a file with the same
name... I want it to automatically overwrite without complaining.

Thanks,
Joe

Use a Macro to Save a Copy of a Workbook?
 

Thanks guys so much! I ended up using Rowan's first example because I
did know which folder I was going to save it in. Very slick. My boss
loves me now.