Save under different name

[Scruffymessiah]

I am trying to find a way of getting excel to automatically save a finished file under a different name. i.e. the same workbook master is used each week with fresh data being added to it by various heads of department, the file is then saved under a different name (usually the department name & then date) and sent for processing by a finance dept. Is there anyway of automating this process so the saved name is uniformed across departments? A lot of these workbooks are used by different departments and this is a way of organising a group of files into a logical order to be processed by finance.

 

[dullboy]

something like this :-

dept = "finance"
dated = Day(Date)
datem = Month(Date)
datey = Year(Date)
fname = dept & dated & datem & datey

Workbooks(2).Worksheets(1).SaveAs Filename:=fname

Workbooks(2).Close

 

[dullboy]

amend workbook(2) and worksheets(1) to correct values or names.

 

[Paul B]

You could use some Marcos and assign them to buttons so each department could just click on the buttons to save there work, something like

Sub Save_As()
ActiveWorkbook.SaveAs FileName:="C:my documentsdepartment name " & Format(Now, "dd-mmm-yy") & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub


_________________<FONT SIZE=4 COLOR="red">Paul B</FONT>
Remember To Always Back Up Your Data Before Trying Something New
Using Excel '97
This message was edited by Paul B on 2002-10-22 06:34

 

[Scruffymessiah]

RE:Dullboy & Paul B

That's lovely.......but how do I impliment these codes?

 

[Scruffymessiah]

Got it! Thanks a lot! (Ignore the post above)

Cheers!