Run Macro

[R1chard]

I have the following Macro:

Private Sub Worksheet_Activate()
'
' Autosort Macro
' Macro recorded 07/02/04 by PCMS GROUP
'autosort
Range("B12:I191").Select
Range("I12").Activate
Selection.Sort Key1:=Range("I12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

How can i get this macro to run automaticaly when i open the workbook. There is only one sheet to the spreadsheet, and I do not want to use a button. I would like the macro to run when the spreadsheet is opened.

Thanks :wink:

 

[Andy W]

Try this

Private Sub Workbook_Open()

' Autosort Macro
' Macro recorded 07/02/04 by PCMS GROUP
'autosort
sheets("enter sheet name here").select
Range("B12:I191").Select
Range("I12").Activate
Selection.Sort Key1:=Range("I12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

 

[Tom Urtis]

To have the procedure run when you open the workbook, place the code in the workbook module as a Workbook_Open event, or in a standard module as an Autpen procedure.

Examples (pick one, do NOT do both):

Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

Sub Workbook_Open()
Range("B12:I191").Sort Key1:=Range("I12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

or

In a standard module:

Sub Autpen()
Range("B12:I191").Sort Key1:=Range("I12"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

It is curious that if you only have one sheet, why you would need to have an open event take place as long as you have a sheet activation event in place for this procedure. The sheet can only be the one activated upon workbook open, so your procedure should fire if you have that code where it belongs in the sheet module.

Anyway, try the above suggestions, and then delete the existing Sheet Activate code because it will be unnecessary, and post back how it worked out for you.

 

[R1chard]

Thank you both. I have it working now :wink: