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.
Thanks,
Andy
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