| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need another eye on this Sub()
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
Hi
I think the statement 'Range("B4").Select' is the problem! No matter which cell has been changed, the macro select B4, and then the If statement will always be true, so elseif will never be tested... Hopes this helps. .... Per "Ayo" skrev i meddelelsen ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
I shortened this code by deleting some things that I thought were
unneccessary but I could be wrong. The bulk of your code depends on the Target Range being B4 or B5 otherwise nothing interesting happens? Maybe that is your issue? Try this code below. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim strFileName As String Dim ws As Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual .DisplayAlerts = False End With Range("B4").Select If Target.Address = "$B$4" Then Range("B5").Select ElseIf Target.Address = "$B$5" Then Application.EnableEvents = False Range("B1").Value = Range("B4").Value & ": " & Range("B5").Value & " " & Range("D4").Value strFileName = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) ActiveWorkbook.SaveCopyAs Range("B4").Value & "_" & Range("B5").Value & "_" & _ strFileName & "_" & Format(Date, "mmmdd_yy") & ".xls" Application.Calculation = xlCalculationAutomatic For Each ws In Worksheets If ws.Name < "BO Download" Then ws.Select Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Range("A2").Select Else ws.Delete End If Next ws Call formulas Range("A3").Select Sheets("Graphes Table").Visible = False ActiveWorkbook.Save Application.EnableEvents = True End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -- Cheers, Ryan "Ayo" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
Good catch... I completely missed that one in favor of the other error I
found in the posted code. -- Rick (MVP - Excel) "Per Jessen" wrote in message ... Hi I think the statement 'Range("B4").Select' is the problem! No matter which cell has been changed, the macro select B4, and then the If statement will always be true, so elseif will never be tested... Hopes this helps. ... Per "Ayo" skrev i meddelelsen ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
Ha ha! I can't believe I didn't see that. I was looking at a few other
things. Rick is right, good catch! -- Cheers, Ryan "Per Jessen" wrote: Hi I think the statement 'Range("B4").Select' is the problem! No matter which cell has been changed, the macro select B4, and then the If statement will always be true, so elseif will never be tested... Hopes this helps. .... Per "Ayo" skrev i meddelelsen ... 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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need another eye on this Sub()
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
