Can anybody please tell me why I am getting the error “Run time Error 9 Subscript out of range” on this line of my code.
The workbook “Approved Suppliers List” opens up OK just cannot seem to activate it
I have even recorded it to make sure all the workbook names are correct, but still the error, Full code below
Any help is very much appreciated
VBA Code:
Windows("Approved Suppliers List.xlsm").Activate
I have even recorded it to make sure all the workbook names are correct, but still the error, Full code below
Any help is very much appreciated
Code:
Private Sub Workbook_Open()
Dim WB As Workbook, wkbk As Workbook
Dim CurrentSheet As Worksheet
Set CurrentSheet = ActiveSheet
Set wkbk = ActiveWorkbook
'Set WB = Workbooks.Open("\\192.168.55.50\waste\Operational Data\_Databases\Approved Suppliers List.xlsm", True, True)
'Application.ScreenUpdating = False ' turn off the screen updating
Windows("Purchase Order list.xlsm").Activate
Sheets("Suppliers").Select
Range("A1").Select
On Error GoTo 0
Range(Selection, Selection.End(xlDown)).ClearContents
0
Windows("Approved Suppliers List.xlsm").Activate
Sheets("Sheet1").Select
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Copy
Windows("Purchase order List.xlsm").Activate
Sheets("Suppliers").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Page 1").Select
' Application.CutCopyMode = False
' WB.Close savechanges:=False ' close the source workbook (Approved suppliers List) without saving any changes
' Wkbk.Activate
' Application.ScreenUpdating = True ' turn on the screen updating
End Sub