Thanks Rick. My main problem is that when I change $B$5 none of the line of
codes following "ElseIf Target.Address() = "$B$5" And Target.Address() < ""
Then" are executed. Thats what I mean by nothing happens.
Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim filename As String, ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

If Target.Address() = "$B$4" And Target.Address() < "" Then
Range("B5").Select

ElseIf Target.Address() = "$B$5" And Target.Address() < "" Then
Application.EnableEvents = False
Range("B1") = Range("B4") & ": " & Range("B5") & " " & Range("D4")
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
ActiveWorkbook.SaveCopyAs Range("B4") & "_" & Range("B5") & "_" &
filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook
If ws.Name < "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
Call formulas
Range("A3").Select
Worksheets("Graphes Table").Visible = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


"Rick Rothstein" wrote:

What do you mean by "when I run this sub"? The code you posted is an event
procedure, not a macro... you don't run it, the Excel's VB system runs it
for you when you make a change to the worksheet. I'll assume you know that,
but I am mentioning it just so I can point out that your code must be in an
individual worksheet module and not a general module as is done with macros.

It would be kind of hard to trace your code completely since you make a call
to an external sub ("formulas") that you did post with your code; however,
the following may be the source of your error. You have this line in your
ElseIf block of code...

For Each ws In Workbook

There is no such object as Workbook... I think you meant ThisWorkbook

--
Rick (MVP - Excel)


"Ayo" wrote in message
...
when I run this sub, it seem like everything is fine except, nothing
whitin
the sub is executed.
What am I missing?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim filename As String, ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Range("B4").Select

If Target.Address() = "$B$4" And Target.Address() < "" Then
Application.EnableEvents = False
Range("B5").Select
Application.EnableEvents = True

ElseIf Target.Address() = "$B$5" And Target.Address() < "" Then
Application.EnableEvents = False
Range("B1") = Range("B4") & ": " & Range("B5") & " " & Range("D4")
filename = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) -
4)
ActiveWorkbook.SaveCopyAs Range("B4") & "_" & Range("B5") & "_" &
filename & "_" & Format(Date, "mmmdd_yy") & ".xls"
Application.Calculation = xlCalculationAutomatic
For Each ws In Workbook
If ws.Name < "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws
Worksheets("BO Download").Select
Worksheets("BO Download").Delete
Call formulas
Range("A3").Select
Worksheets("Graphes Table").Visible = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

.