code fails when using task scheduler only

AndyM90

New Member
Joined
Jul 25, 2014
Messages
47
Hi All,

I have a macro which consolidates data from a range of spreadsheets into one ("document.xlsm"). Currently, this macro works well and runs based on a button i've created. However, I now want this macro to run automatically before I get into the office in the morning so that the data is immediately available for review.

To do this, I have used the Task Scheduler to open a generic document I've saved on my desktop. In that document there is the below code which opens up my consolidated doc ("Document.xlsm") and runs the code which I have copied from my original macro (open other docs, copy data, paste it, sort it etc).

However when I put this into the other workbook it fails (run time error 'i': subscript out of range) and highlights the Sheets(Array( code. Anyone know why this is happening in the new automatic opening macro and not in the original code?


When i end the macro, it returns to the document I want to clear the data from and shows all the sheets unhidden so I dont understand how it can't find the sheets to clear the data.

Code:
Private Sub Workbook_Open()
'open the consolidated doc

Workbooks.Open Filename:="Document.xlsm"
Workbooks("Document.xlsm").Activate


Application.CutCopyMode = False
Application.DisplayAlerts = False


'unhide any hidden sheets
Dim sh As Worksheet
 Dim sh2 As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Sheets
sh.Visible = xlSheetVisible
Next

'clear the previous data from the sheets

Sheets(Array("E", "T", "C", "1", "2"etc...")).Select
Cells.Select
Selection.ClearContents


Thanks,

Andy
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

AndyM90

New Member
Joined
Jul 25, 2014
Messages
47
Ok so i think this might be being caused by the code being in the 'Workbook" area rather than general. Is there a way I can have code that essentially says: when this workbook is opened, find the macro in Module 6 called "my macro" and run it? Rather than me having the code as shown in the above post.

Thanks,
 

Watch MrExcel Video

Forum statistics

Threads
1,108,611
Messages
5,523,889
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top