0

I am running a timed macro every half hour using this code:

Private Sub Workbook_Open()

MsgBox ("test")

Application.OnTime TimeValue("07:30:00"), "'Test.xlsm'!Daily.Daily"


End sub

This runs fine when I manually call the macro with a button, or I have the sheet active but I sit with multiple sheets open at once, when I am clicked into another sheet the runtime error 9 appears, subscript out of range - I understand this error, but dont know how I can either run it in the background or bring the sheet into the forefront so it isnt out of range

I have tried various online forum suggestions but they seem to be advising how to solve the runtime error due to a different cause than I am facing, im sure its a simple fix but I cant seem to ask google the right question - any suggestions would be great. thank you

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
VBAKeith
  • 3
  • 3
  • 3
    Without knowing what the `'Test.xlsm'!Daily.Daily` code does I can't give a definite answer. But my guess is that within that sub there are instances where `activesheet` or `activecell` or even `activeworkbook` are used. this will throw an error when the workbook is in the background, as the right workbook isn't active and it can't find the expected data. – Plutian Jan 18 '23 at 13:47
  • the daily macro that is being called just simply copies dynamic data at set intervals of 30 minutes and adds it to a list of data. The active terms you mention are familiar, but I am unsure how to make this sheet become the activeworkbook out of all those open – VBAKeith Jan 18 '23 at 14:11
  • 1
    To rewrite your code please review [this question and answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) which is related. If any of the "bad" methods in this answer are used in your code, it's most likely the culprit as to why your code fails. If you rewrite it, it is entirely possible to run it completely in the background. – Plutian Jan 18 '23 at 14:16

0 Answers0