Search code examples
excelvbaworksheet-function

Unable to set worksheet names to variables, returns "Run-time error '9': Subscript out of range"

I am working on a project to filter a number of worksheets/columns. First step is to identify the number of rows with values in the first column of the "Iris Data" worksheet, and build around that (as this will be used again with new data, deleted, and done again, etc.). The code I have now does not work, and returns the error "Run-time error '9': Subscript out of range". Because many examples use a generic "Sheet1", I tried copy/pasting the same data into a new sheet named "Sheet1" and it was successful. Unfortunately re-naming the sheets is not an option according to my supervisor. I could probably get away with changing it a little bit, but not much.

So my question is, is this a naming issue with the space in 'Iris Data'? Some other issue?

I tried changing to 'IrisData' or 'Iris_Data' and that is also returning RunTime error 9. I also tried setting 'Sheet1' = "Iris Data", and that also returns RunTime error 9. 

Sub Helping_Out_Alex()

Dim wb As Workbook              'identify source workbook
Dim IrisData As Worksheet       'identify Iris Data worksheet
Dim xlApp As Object             'tie workbook/worksheet into an object
Dim lastRow As Long             'identify numerical value for # of rows
Dim Sheet1 As String            'issue with spaces in name, work-around

Sheet1 = "Iris Data"            'Added this as a work around

Set xlApp = CreateObject("Excel.Application")    'set variable to object                                                         
Set wb = 
xlApp.Workbooks.Open("\\uk1.group.internal\data\PERSONAL\GorowsR\My 
Documents\Equiniti\Helping_Out_Alex.xlsm")       'set varaible to workbook

Set IrisData = wb.Worksheets("Sheet1")           'set variable to first sheet

With xlApp                 'Example code                                                   
    .Visible = True        'Example code                                             
    .EnableEvents = True   'Example code                                           
End With                   'Example code                                           

With ActiveSheet
lastRow = IrisData.Cells(.Rows.Count, "A").End(xlUp).Row            'This is the variable I'm trying to define as a number so I know the range each time
    MsgBox "the value is" & lastRow
End With

End Sub
Solution

  • The line 

     Set IrisData = wb.Worksheets("Sheet1")

    instructs excel to point to a sheet whose name (as shown in the excel tab at the bottom of the screen) is Sheet1. You can also point to a sheet by its code name (shown in the vbeditor project explorer window) so

    Set IrisData = wb.Worksheets(Fred)

    Or by it's index number, where the first sheet on the left is 1: so

     Set IrisData = wb.Worksheets(1)

    Your problem seems to be that your sheet is actually called something like "Iris Data" so you need either 

       Set irisdata = wb.worksheets("Iris Data")

    or (probably simpler) since you say it's the first sheet, just

      Set IrisData = wb.Worksheets(1)