When I tried to get excel to choose which workbook to use, it gives me an error for subscript out of range. I don't know whether I have to reference which folder it originates from. They are all in the same folder. I looked through other peoples solutions,but I don't have neither the same format nor task. The error is on the line where it assigns workbook numbers
Sub bringbookstogether()
Dim currentsheet As Worksheet
Set currentsheet = Application.ActiveSheet
Dim othersheets As Worksheet
Dim wbook As Workbook
Dim c As String
'assigns the number to start with
Dim a, b, d As Integer
a = 4
b = 6
d = 1
'assigns workbook numbers
If (d = 1) Then
Set wbook = Workbooks("MaintPrep Sheet 1st")
Else
If (d = 2) Then
Set wbook = Workbooks("MaintPrep Sheet 2nd")
Else
If (d = 3) Then
Set wbook = Workbooks("MaintPrep Sheet 3rd")
End If
End If
End If
'End if it's done with all the workbooks
Do Until (d = 4)
'Looks for the sheet that has the same name
Do While (c = currentsheet.Name)
'Ends in row 99
Do While (b < 99)
'Ends in Column 52
Do While (a < 52)
currentsheet.Cells(b, a) = currentsheet.Cells(b, a) + Workbooks(d).Sheets(c).Cells(b, a)
a = a + 1
Loop
b = b + 1
Loop
Loop
d = d + 1
Loop
End Sub
First of all, it's better to use the full filename: Workbooks("MaintPrep Sheet 1st.xlsx") etc.
Second of all, this code will error as soon as one of the Workbooks you're trying to access is not currently opened. If a Workbook is not open, it doesn't exist within the current context and thus Excel will throw error 91.
To fix this, you could do:
Sub a()
Dim wb As Workbook
On Error Resume Next 'To avoid error 91
Set wb = Workbooks("MaintPrep Sheet 1st.xlsx")
On Error GoTo 0 'To avoid not seeing other errors.
If Not wb Is Nothing Then
'Do stuff
MsgBox "Opened!"
Else
'Handle the fact that it's missing
MsgBox "Not open!"
End If
'Alternatively, OPEN the workbook if you couldn't set it in the first place:
On Error Resume Next
Set wb = Workbooks("MaintPrep Sheet 1st.xlsx")
On Error GoTo 0
If wb Is Nothing Then
Set wb = Workbooks.Open("C:\FullPath\MaintPrep Sheet 1st.xlsx")
'Do stuff
End If
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments