Run Code when Workbook opens

gbaker · #1 07-10-2012, 05:39 AM

How do I set up code so that when my workbook opens is automatically runs it. I tried to put the code into ThisWorkbook but it doesn't work.
Thanks in advance for your help!!!

GWB

Code:

Sub pvalue()
'
' pvalue Macro
'
'
Workbooks(1).Activate

    Sheets("PRINT VIEWING CALENDAR").Select
    Sheets("PRINT data").Visible = True
    ActiveWindow.ScrollColumn = 4
    Columns("E:CA").Select
    Selection.Copy
    Sheets("PRINT VIEWING CALENDAR").Select
    Columns("E:CA").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("PRINT data").Select
    ActiveWindow.SelectedSheets.Visible = False
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 2
    Range("D1").Select
End Sub

Colin Legg · #2 07-10-2012, 09:29 AM

Hi,

You can't put any old code in the ThisWorkbook module and expect it to run when the workbook opens: you have to specifically tell Excel that you want it to run when the workbook opens.

An Excel workbook has a Workbook_Open() event handler: this is the VBA code it will run when the workbook is opened. In the visual basic editor, double click on the ThisWorkbook module in the project explorer. Then, just above the code pane you'll notice two dropdown boxes (see picture). In the left hand one choose workbook. In the right hand one choose Open (you don't actually need to do this because the Workbook_Open() event handler will be automatically chosen for you) - while you're there have a look at some of the other Workbook event handlers which are available because they might be useful for you in the future. Once this is done, a Workbook_Open() event handler will be created for you in the code pane. You can put whatever code you want to run inside it, or you can call your existing procedure from it.

gbaker · #3 07-10-2012, 10:41 AM

Hi Colin,
Again you made it easy. Thanks for your help.
Question:
I never was able to get the code you provided to sort my sheet the way I need it too.
Here is the code and I attached a workbook to show what I'm looking for. I really don't understand this one. Maybe you can explain and tell me what I need to do.

Code:

Sub SortDaysProvider()
    Dim DayRange As Long
    Dim TopRow As Long
    Dim sRange As Range
    Dim fRange As Range
 
    Application.ScreenUpdating = False
 
    For DayRange = 1 To 365
        TopRow = (DayRange * 17) + 9
 
        With ActiveWorkbook.Worksheets("Input Calendar (2012)")
 
            Set sRange = .Range("B" & TopRow & ":" & "O" & TopRow + 5)
            Set fRange = .Range("B" & TopRow & ":" & "B" & TopRow + 5)
 
            With .Sort
                .SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal
                 .SetRange sRange
                 .Header = xlGuess
                 .MatchCase = False
                 .Orientation = xlTopToBottom
                 .SortMethod = xlPinYin
                 Application.Calculate
                 .Apply
                 .SortFields.Clear
            End With
        End With
    Next DayRange
 
    Application.ScreenUpdating = True

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word opens with 2 pgs	jmacky	Word	4	01-28-2013 09:10 AM
#REF! error when opening a workbook that contain a refference to another workbook	tanababa	Excel	2	06-07-2012 03:11 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook?	tinfanide	Excel Programming	1	02-09-2012 04:08 PM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM