code fails when using task scheduler only

[AndyM90]

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.

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

 

[AndyM90]

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,