how to auto change a file name?

[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 = D355 = 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

 

[Bob Phillips]

Steve,

Here is one way

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sFile As String
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
With Target
iPos = Application.Match(.Value, Me.Range("B3:B55"), 0)
sFile = "Week No." & .Value & " (" & _
Format(Application.Index(Me.Range("D355"), iPos),
"dd.mm.yyyy") & _
")."
ThisWorkbook.SaveAs Filename:=sFile
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


It is worksheet event code, so right-click on the sheet tab, select View
Code from the menu, and past it in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steven]

Thanks Bob, very much appreciated

Steve

Steve,

Here is one way

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sFile As String
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
With Target
iPos = Application.Match(.Value, Me.Range("B3:B55"), 0)
sFile = "Week No." & .Value & " (" & _
Format(Application.Index(Me.Range("D355"), iPos),
"dd.mm.yyyy") & _
")."
ThisWorkbook.SaveAs Filename:=sFile
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


It is worksheet event code, so right-click on the sheet tab, select View
Code from the menu, and past it in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steven]

Bob, the code did not work at first, made some changes and now it does!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sFile As String
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
iPos = Application.Match(.Value, Me.Range("B3:B55"), 0)
sFile = "Week No." & .Value & " (" & _
Format(Application.Index(Me.Range("E3:E55"), iPos),
"dd.mm.yyyy") & _
")."
ThisWorkbook.SaveAs Filename:=sFile
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Many thanks,
Steve

 

[Bob Phillips]

Sorry, didn't pay enough attention to where you defined the data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steven]

Bob how can i specify the location for the file to be saved ? currently its
saving to My Documents.

Many thanks,
Steve

 

[Bob Phillips]

Steve,

You could try a couple of ways, and see which suits best.

First, use the GetSaveFileName method, which allows you to browse and input
a filename

Dim sFile

sFile = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Workbooks (*.xls), *.xls")
If sFile <> False Then
ActiveWorkbook.SaveAs Filename:=sFile
End If

or you can simply change the directory

ChDrive "C:\Steven\Test"
ChDir "C:\Steven\Test"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steven]

Where in the code would i insert ChDrive "C:\Steven\Test" ChDir
"C:\Steven\Test" ? or the GetSaveFileName method ?

Many thanks Bob,

Steve

 

[Bob Phillips]

The first method would replace the line

ThisWorkbook.SaveAs Filename:=sFile

The second would be immediately before it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steven]

Thanks Bob for the continued support

Steve

The first method would replace the line

ThisWorkbook.SaveAs Filename:=sFile

The second would be immediately before it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

It's a pleasure. I try to keep a handle on the thread, just in case<g>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)