Subscript Out of Range

[alansidman]

Been looking at this and cannot see my issue.


Option Explicit


Sub works()
Dim i As Integer, c As Range
Dim Path As String, File As String
Dim wkb As Workbook


For i = 1 To 3
Path = " C:\Users\Alan\Desktop\Works" & i & "\"
Set wkb = Path & Workbooks("Works" & i & ".xlsx")
c = wkb.Sheets("Sheet1").Range("A1")


Workbooks.Open wkb
c.Value = i
ActiveWorkbook.Close
Next i
End Sub

Getting Error Message "Subscript Out of Range" on this line


Set wkb = Path & Workbooks("Works" & i & ".xlsx")


Have checked file names to ensure that they are named correctly. Folders are named correctly. What am I missing here?

Alan

[SamT]

Set wkb = Path & Workbooks("Works" & i & ".xlsx")
What am I missing here?
That is trying to set a variable to an open Excel Workbook, in fact: Workbooks(" C:\Users\Alan\Desktop\Works1\Workbooks(Works1.xlsx)"). Also note the empty space between the quote mark and the C:


It looks like you have a workbook "Works1.xlsx" in subfolder "Works1"

Sub works_now_maybe()
Dim i As Integer, c As Range
Dim WkbPath As String 'Path is a VBA Keyword. Avoid using KeyWords as Variable names
Dim wkb As Workbook

For i = 1 To 3
WkbPath = "C:\Users\Alan\Desktop\Works" & i & "\Works" & i & ".xlsx"
Workbooks.Open WkbPath
Set wkb = ActiveWorkbook
Set c = wkb.Sheets("Sheet1").Range("A1")
c.Value = i
wkb.Close
Next i

'Alternate
For i = 1 To 3
WkbPath = "C:\Users\Alan\Desktop\Works" & i & "\Works" & i & ".xlsx"
Workbooks.Open WkbPath
Set wkb = ActiveWorkbook
wkb.Sheets("Sheet1").Range("A1") = i
wkb.Close
Next i

End Sub

[alansidman]

Thanks SamT.