Consulting

Results 1 to 8 of 8

Thread: Autorun a macro when workbook is open

  1. #1
    VBAX Regular
    Joined
    Sep 2008
    Posts
    29
    Location

    Autorun a macro when workbook is open

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Call should have worked. Show us the code.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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/]

  6. #6
    Goofed on the code tag

  7. #7
    [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]

  8. #8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •