Hello folks,
The below code loops through several .xlsm workbooks, calling the Startx macro for each workbook.
Most times it works perfectly, but, eventually, I get the following error:
Run-time error '9':
Subscript out of range
Then on this same message box there are the buttons: End, Debug and Help
When I click on Debug, it highlights this part of below code:
Application.Run ("'" & MyFile & "'!Startx")
Then I simply need to click on Play button, and it continues the macro until it completes successfully.
In order to try to fix this issue I tried to minimize the screen of AllWorkbooks macro, but it did not work.
Does anybody has any idea on what I need to do to solve this?
Sub AllWorkbooks()
Dim MyFolder As String
Dim MyFile As String
Dim wbk As Workbook
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer
SetDateB = Cells(5, 2).Value
SetDateB = "'" & SetDateB
ActiveWindow.WindowState = xlMinimized
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please Select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\"
End With
MyFile = Dir(MyFolder)
Do While MyFile <> ""
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
'Worksheets("Start").Activate
Worksheets("Start").Select
Cells(5, 2).Value = SetDateB
Application.Run ("'" & MyFile & "'!Startx")
Application.DisplayAlerts = False
wbk.Close True
Application.DisplayAlerts = True
'wbk.Close savechanges:=False
'wbk.Close savechanges:=True
MyFile = Dir
Application.Wait Now + TimeValue("00:00:10")
Loop
'Determine how many seconds code took to run
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
ActiveWindow.WindowState = xlMaximized
'Notify user in seconds
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
If Application.Workbooks.Count = 1 Then
Application.Quit
Else
ActiveWorkbook.Close
End If
End Sub
Bookmarks