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

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("D3:D55"), 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" wrote in message news:u8Knc.123$Kw6.2@newsfe1-win...
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

Thanks Bob, very much appreciated

Steve

"Bob Phillips" wrote in message
...
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("D3:D55"), 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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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" wrote in message
...
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("D3:D55"), 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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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" wrote in message
...
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" wrote in message
...
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("D3:D55"), 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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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

Many thanks,
Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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" wrote in message news:r5Nnc.22$5N2.19@newsfe6-win...
Bob how can i specify the location for the file to be saved ? currently
its
saving to My Documents.

Many thanks,
Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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" wrote in message
...
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" wrote in message
news:r5Nnc.22$5N2.19@newsfe6-win...
Bob how can i specify the location for the file to be saved ? currently
its
saving to My Documents.

Many thanks,
Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
news:r5Nnc.22$5N2.19@newsfe6-win...
Bob how can i specify the location for the file to be saved ?
currently
its
saving to My Documents.

Many thanks,
Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

Thanks Bob for the continued support

Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
news:r5Nnc.22$5N2.19@newsfe6-win...
Bob how can i specify the location for the file to be saved ?
currently
its
saving to My Documents.

Many thanks,
Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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

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)

"Steven" wrote in message
...
Thanks Bob for the continued support

Steve

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
news:r5Nnc.22$5N2.19@newsfe6-win...
Bob how can i specify the location for the file to be saved ?
currently
its
saving to My Documents.

Many thanks,
Steve

"Bob Phillips" wrote in
message
...
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" wrote in message
...
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" wrote in
message
...
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("D3:D55"),
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" wrote in message
news:u8Knc.123$Kw6.2@newsfe1-win...
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