Running macro at set time disables range selection

[batyushki]

I have a macro that needs to run at a set time every day. But when it runs, range.select is disabled -- all of the macro's selections occur in the active cell.

Here is how it is set up:

1. My PERSONAL file has an on_time statement that opens the spreadsheet containing the macro at a fixed time.
2. The spreadsheet with the macro has a workbook_open statement that calls the macro.

Why is selection disabled? Here is the code.

PERSONAL.xls, This Workbook code:


Private Sub Workbook_Open()
Application.OnTime TimeValue("8:37 AM"), "autorun_rp_export"
End Sub

Sub autorun_rp_export()
Application.DisplayAlerts = False
Workbooks.Open FileName:= _
"\\psafilc\medcadat\merch\International\Distribution\NewAllocation\RP Files\ExportRP.xls" _
, ReadOnly:=True
Application.DisplayAlerts = True
End Sub


Then in the spreadsheet with the macro:


Private Sub Workbook_Open()
Call export_rp
End Sub

Sub export_rp()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.Calculate

currbook = ActiveWorkbook.Name

Worksheets("Export").Activate
ActiveSheet.Unprotect
Range("B11:D10000").ClearContents

Range("G:G").ClearContents
Range("G11").Select

... etc.

This last select statement, and all other select statements in the macro, don't work. Excel doesn't return an error, but the macro keeps working in the same active cell.

 

[BrianB]

Private Sub Workbook_Open()

.... does not work in a worksheet module. You probably mean ...Private

Sub Worksheet_Activate()

.. or whatever.

 

[batyushki]

Actually that piece of code is in the Workbook part of the code, so it should work. When the workbook opens it is automatically calling the macro that is in the module.

 

[BrianB]

That is not what you said. Quote "Then in the spreadsheet with the macro: ..."

Either way you seem to have put it in *twice* somehow. Excel will only run a single instance .. the first one it finds in the ThisWorkbook code module.