Thanks for all the pointers Ryan. I will try them now.

"Ryan H" wrote:

You may want to double check a few things.

1.) Put this in the Immediate Window: Application.EnableEvents = True.
This will ensure that the events weren't turned off which will keep the
Change Event from firing.

2.) Try this code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim filename As String
Dim ws As Worksheet

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

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

ElseIf Target.Address = "$B$5" 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.Calculate

For Each ws In ThisWorkbook.Worksheets
If ws.Name < "BO Download" Then
ws.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Range("A2").Select
End If
Next ws

Sheets("BO Download").Delete
Call formulas
Range("A3").Select
Sheets("Graphes Table").Visible = False
ActiveWorkbook.Save
End If

With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

End Sub

3.) Why do you have Target.Address() = "$B$5" And Target.Address() < ""?
I would just put Target.Address = "$B$5", because your Target will always
have an address.

4.) Change For Each ws In ThisWorkbook with For Each ws In
ThisWorkbook.Worksheets

5.) You don't neccessarly have to select Sheets("BO Download") to delete it
either so I would change,

Worksheets("BO Download").Select
Worksheets("BO Download").Delete

to

Sheets("BO Download").Delete

6.) I also see you change the Calculation mode from manual to automatic
back to manual. Why is that? Are you just wanting to calculate your
worksheet in that 2 line of code? Instead of

Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

just use

Application.Calculate

Have I confused you yet? lol Hope this helps, if so, let me know, click
"YES" below.

--
Cheers,
Ryan


"Ayo" wrote:

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

.