For i = 1 To 8
Set OtherWB = Workbooks.Open("C:\Folder\SubFolder\" & i & ".xls") '// Change path as required
For Each WS in OtherWB.Sheets
WS.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
Next WS
OtherWB.Close False
Set OtherWB = Nothing
Next i
For i = 1 To 8
With CreateObject("C:\Users\justin\Desktop\" & i & ".xls").Sheets("Cyber Rain Controller Event")
.Name = i
.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End With
Next i
For i = 1 To 8
Set OtherWB = Workbooks.Open("C:\Users\justin\Desktop\" & i & ".xls")
With OtherWB.Sheets("Cyber Rain Controller Events")
.Name = i
.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End With
OtherWB.Close False
Set OtherWB = Nothing
Next i