how to auto change file name ?

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

How could i make a workbook change its file name once data has been entered
in a curtain cell?

example:

file name format would be, Week No.# (Date on sunday of that week).xls

all the data is stored in a list like this:

wk_num = B3:B55 = 1:53
week/start = D3:D55 = 29/03/2004:28/03/2005
week/end = E3:E55 dates = 04/04/2004:03/04/2005

if the current week is 7, user inputs 7 in cell A1 workbook now renames to,
Week No.7 (16.05.2004).xls

hope that makes sense, many thanks for any help,

Steve
 
Steven,

You can use a macro:

Sub SaveWithNewName()
Dim myDate As Date
Dim myFName As String

If Range("A1").Value <> "" Then
myDate = Application.VLookup(Range("a1").Value, Range("B3:D55"), 3, False)
myFName = "Week No. " & Range("A1").Value & _
Format(myDate, "(dd.mm.yyyy)") & ".xls"
ThisWorkbook.SaveAs myFName
End If
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top