Printable View
Hello...I need help with a piece of VBA code that will automatic run one of my Macro (macro name is 'Update_Chart' when workbook is open. Not sure if you need to know the workbook name but it will always start with 'Sales Forecast'..then followed by Month and Year. So, in this case, this will be 'Sales Forecast October 2008.xls'. I appreciate your help with this. Thank you.
[vba]
Private Sub Workbook_Open()
'your code
End Sub
[/vba]
This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code
I have an existing macro and want to add this feature. I tried "Call" and "Application.Run" but neither seems to work. Is there another way to get my existing macro to run when the workbook is opened?
Call should have worked. Show us the code.
I also tried to paste the code itself into the workbook module with your WorkBook_Open() and still no results
[vba]Sub ExpeditingUpdate()
Sheets("Temp").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Clear
Sheets("Combined Projects").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Select
Selection.Copy
Sheets("Temp").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Sheets("Temp")
.Range("A5:M" & Range("B2000").End(xlUp).Row).Sort Key1:=.Range("C5"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With
Sheets("Combined Projects").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Clear
Call GatherInfo
With Sheets("Combined Projects")
.Range("A5:M" & Range("B2000").End(xlUp).Row).Sort Key1:=.Range("J5"), _
Order1:=xlAscending, Key2:=.Range("E5"), Order2:=xlAscending, Key3:=.Range("A5"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With
Sheets("Combined Projects").Select
Range("K5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],Temp!C[-8]:C[2],9,FALSE)"
Selection.AutoFill Destination:=Range("K5:K5000"), Type:=xlFillDefault
Range("L5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],Temp!C[-9]:C[2],10,FALSE)"
Selection.AutoFill Destination:=Range("L5:L5000"), Type:=xlFillDefault
Range("K5:L5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L5:L5000").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A2").Select
End Sub
Sub GatherInfo()
Dim i As Integer
For i = 3 To Sheets.Count
With Sheets(i)
.Range("A5:M" & .Range("B2000").End(xlUp).Row).Copy
Sheets("Combined Projects").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
Next
End Sub
[vba/]
Goofed on the code tag
[vba]Sub ExpeditingUpdate()
Sheets("Temp").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Clear
Sheets("Combined Projects").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Select
Selection.Copy
Sheets("Temp").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Sheets("Temp")
.Range("A5:M" & Range("B2000").End(xlUp).Row).Sort Key1:=.Range("C5"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With
Sheets("Combined Projects").Select
Range("A5:M" & Range("B2000").End(xlUp).Row).Clear
Call GatherInfo
With Sheets("Combined Projects")
.Range("A5:M" & Range("B2000").End(xlUp).Row).Sort Key1:=.Range("J5"), _
Order1:=xlAscending, Key2:=.Range("E5"), Order2:=xlAscending, Key3:=.Range("A5"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End With
Sheets("Combined Projects").Select
Range("K5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],Temp!C[-8]:C[2],9,FALSE)"
Selection.AutoFill Destination:=Range("K5:K5000"), Type:=xlFillDefault
Range("L5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-9],Temp!C[-9]:C[2],10,FALSE)"
Selection.AutoFill Destination:=Range("L5:L5000"), Type:=xlFillDefault
Range("K5:L5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L5:L5000").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A2").Select
End Sub
Sub GatherInfo()
Dim i As Integer
For i = 3 To Sheets.Count
With Sheets(i)
.Range("A5:M" & .Range("B2000").End(xlUp).Row).Copy
Sheets("Combined Projects").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
Next
End Sub
[/vba]
xld,
If you think "Call" should have worked and with the code pasted in the module and since"Application.Run" did not work then it is probably because of the remote server location which has been messing up a couple of my macros that did work when the server was on-site here. If you don't see any real obvious problem then do worry about this. I just thought it would be nice to have but I can just have the people who use the file call the macro manually. Thanks for the effort.