VBA: Run-time Error '9': Subscript out of range

[TheCobbler]

Hi - Getting the above error when I try to use CORfname that I have as a global variable from a different module which captures a path. (Public CORfname As Variant)
Can see that CORfname in this module is finding the right path in the immediate window so not sure what the issue is. Any ideas? Cheers, Cobb

Workbooks(CORfname).Activate: This line highlights when debugging.

Option Explicit

Sub L_Shop_Check()

Workbooks(CORfname).Activate
Sheets("To Order").Activate

'Deletes Blank Rows

    On Error Resume Next
    Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
    
    Dim SkuRng As Range
    Dim SkuCell As Range
    
'Inserts Sku Preface

    Range("B1").EntireColumn.Insert

    Set SkuRng = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)

        For Each SkuCell In SkuRng

            If Not IsEmpty(SkuCell) Then

                SkuCell.Offset(, -1) = "_" & SkuCell

            End If
        Next

    Cells(1, 2) = "Merged SKU"
    
    
End Sub

 

[RoryA]

You do not include a path when referring to a workbook by name in the Workbooks collection.

 

[TheCobbler]

Ok I understand. Is the best way to proceed to extract the Workbook name and then reference that? Or is there a way to activate the Workbook based on path? Thanks

 

[RoryA]

It somewhat depends on how you get the filename to start with. It may be easier to obtain the path and filename separately to start with, or it may be easier to extract the filename later. It may also be easier, if the code opens the workbook, to store a reference to that Workbook object in another variable.

 

[TheCobbler]

Cool. I'm currently getting the name when a user chooses a file, utilising the following code in a separate module if that sheds any light.

Public CORfname As Variant

Sub B_Check_Sales()

'Allows selection of the Checkout Report

    CORfname = Application.GetOpenFilename(FileFilter:="Excel Workbooks,*.*", Title:="Open the Checkout Report...", MultiSelect:=False)
    
        If CORfname <> False Then
            Workbooks.Open Filename:=CORfname

        End If
End Sub

 

[RoryA]

In that case, I'd use a Workbook object:

Public SourceWorkbook as Workbook

and then amend that code to use:

Set SourceWorkbook = Workbooks.Open(Filename:=CORfname)

You can then simply refer to SourceWorkbook whenever you need that workbook as an object.

 

[TheCobbler]

Yep that's great. Thanks for helping me understand and your time!