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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,595
Messages
5,832,634
Members
430,149
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top