Excel Macro Stop running

[Arunkumar Sekar]

Hi ,

I am using excel sheet to get the reuters market price real time.I used macro to schedule and copy the current worksheet and saveCopy as another work sheet.

But After some times if any one open the other excel sheet,and open the excel option window,my macro stopped running.

Please help me

Private Seconds As Integer

Sub Workbook_Open()
    Seconds = 5
    ScheduleSaving
End Sub

Sub ScheduleSaving()
    Application.OnTime Now + Seconds / 24 / 3600, "ThisWorkbook.SaveMyWorkbook", Now + 1.5 * Seconds / 24 / 3600
End Sub

Sub SaveMyWorkbook()
    Application.DisplayAlerts = False
        ThisWorkbook.SaveCopyAs "C:\OSTS\Reuters_tmp.xls"
    Application.DisplayAlerts = True
    ScheduleSaving
End Sub

 

[Marc L]

Hi !

When posting a code, as per forum rules use code tags
or just the code icon ! Thanks …

If anyone open the other workbook, the workbook can't be saved !
And raises the error …
Try with On Error Resume Next statement but data will be lost in this case
or just think about a new strategy for the workbook name
or just detect if workbook is already opened …
​

 

[Arunkumar Sekar]

Thank you Marc noted.

Is there any way to specify the macro only for that sheet ? .If i open other sheets also ,the macro is available to view and execute

 

[Marc L]

In your code there is nothing about a sheet !

Save the workbook as .xlsx format (so without code) or
instead of directly saving the workbook,
copy the data worksheet to a new workbook
(see Worksheets.Copy in VBA inner help)
then save this new workbook …
​