cannot retrieve column titles from another workbook

Question

I am trying to copy the string values(column titles) from another workbook in row 4 as captions for checkboxes in the workbook where I am running the code. This is what I have so far and it is not working because it is showing the error message "Subscript out of range, run time error 9" Here is what I have. After the error message pops up the line marked below is highlighted. Can anybody help me please. Thank you very much.

 Function CallFunction(SheetName As Variant) As Long

 Dim text As String
 Dim titles(200) As String ' Dim titles(200) As String ' Array
 Dim nTitles As Integer
 Dim wks As Worksheet
 Dim myCaption As String

 PathName = Range("F22").Value
 Filename = Range("F23").Value
 TabName = Range("F24").Value

 ControlFile = ActiveWorkbook.Name
 Workbooks.Open Filename:=PathName & "\" & Filename
 ActiveSheet.Name = TabName

 Set wks = Workbooks("Filename").Worksheets(SheetName).Activate  ' <= Highlights this line ****

 For i = 1 To 199
     If Trim(wks.Cells(4, i).Value) = "" Then
        nTitles = i - 1
        Exit For
    End If
        titles(i - 1) = wks.Cells(4, i).Value
 Next

 i = 1

 For Each cell In Range(Sheets("Sheet1").Cells(4, 1), Sheets("Sheet1").Cells(4, 1 + nTitles))

    myCaption = Sheets("Sheet1").Cells(4, i).Value

    With Sheets("Sheet1").checkBoxes.Add(cell.Left, _
        cell.Top, cell.Width, cell.Height)
        .Interior.ColorIndex = 12
        .Caption = myCaption
        .Characters.text = myCaption
        .Border.Weight = xlThin
        .Name = myCaption
    End With

    i = i + 1
 Next
 End Function

Subscript out-of-range typically indicates that a specified Worksheet does not exist in the workbooks `Worksheets` collection. — David Zemens, Jul 07 '14 at 20:57

score 2 · Accepted Answer · edited May 23 '17 at 10:26

Subscript out-of-range typically indicates that a specified Worksheet does not exist in the workbooks Worksheets collection.

Otherwise, are you sure that the workbook specified by FileName is already open? If not, that will raise the same error.

Ensure that A) the file is already open (or use the Workbooks.Open method to open it), and B) ensure that such a worksheet already exists (if not, you will need to create it before you can reference it by name).

Update

You have Workbooks("FileName") where "Filename" is a string literal. Try changing it to simply Filename (without the quotation marks) (this seems like the OBVIOUS error).

Also worth checking:

I also observe this line:

ActiveSheet.Name = TabName

If the sheet named by SheetName is active when the workbook opens, then that line will effectively rename it, so you will not be able to refer to it by SheetName, but instead you would have to refer to it by Worksheets(TabName). ALternatively, flip the two lines so that you activate prior to renaming:

Set wks = Workbooks(Filename).Worksheets(SheetName).Activate  
ActiveSheet.Name = TabName

For further reading: avoid using Activate/Select methods, they are confusing and make your code harder to interpret and maintain:

How to avoid using Select in Excel VBA macros

If that is the case, then you could do simply:

Workbooks(Filename).Worksheets(SheetName).Name = TabName

Also possible that `ActiveSheet.Name = TabName` is actually renaming the worksheet that you're expecting to activate a few lines later. — David Zemens, Jul 07 '14 at 21:05

cannot retrieve column titles from another workbook

1 Answers1