Runtime Error 1004 when selecting sheet by name VBA

Question

I have been developing an Excel macro for my company that opens several workbooks, parses them for a specific line of information, stores that line, then once it has gone through each workbook sets the value of a horizontal selection of cells in a single workbook on one of two pages. The issue I am having is upon trying to select the second page I need to put data on to i get a runtime error 1004.

Here is the code;

Sub sortandinsert(listie As Variant)
'Takes in the data array and sorts it as it inserts it into the spreadsheet.
'Expects a 2 dimensional array.
Dim serialarray() As Variant
Dim listlen1 As Integer
Dim listlen2 As Integer
Dim listlen3 As Integer
Dim count1 As Integer
Dim count2 As Integer
Dim SSCcurrentrow As Integer
Dim DSCcurrentrow As Integer
Dim Colstart As Integer
Dim SSCcounter As Integer
Dim DSCcounter As Integer
Dim actbook As Workbook
Dim selectrange As range
Set actbook = ActiveWorkbook
SSCcounter = 0
DSCcounter = 0
Colstart = 1
SSCcurrentrow = 10
DSCcurrentrow = 10
serialarray = FindSerial(listie, serialarray)
listlen1 = findlength(serialarray)
For count = 0 To listlen1 - 1
    MsgBox serialarray(count)
Next
With actbook
    listlen2 = findlength(listie)
    For count1 = 0 To listlen1 - 1
    MsgBox "Current Serial is" & " " & serialarray(count1)
        For count2 = 0 To listlen2 - 1
            If contains(listie(count2), CStr(serialarray(count1))) Then
            listlen3 = findlength(listie(count2))
                If listie(count2)(0) = "SSC" Then
                    Set selectrange = Sheets("SSC").range(Cells(SSCcurrentrow + SSCcounter, Colstart), Cells(SSCcurrentrow + SSCcounter, Colstart + listlen3 - 1))
                    With selectrange
                        .Value = listie(count2)
                    End With
                    SSCcounter = SSCcounter + 1
                ElseIf listie(count2)(0) = "DSC" Then
                    Set selectrange = Sheets("DSC").range(Cells(DSCcurrentrow + DSCcounter, Colstart), Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
                    With selectrange
                        .Value = listie(count2)
                    End With
                    DSCcounter = DSCcounter + 1
                End If
            End If
        Next
        SSCcurrentrow = SSCcurrentrow + SSCcounter + 6
        DSCcurrentrow = DSCcurrentrow + DSCcounter + 6
        'SSCcounter = 0
        'DSCcounter = 0
    Next
End With
End Sub

The portion of the code where the error arises is;

Set selectrange = Sheets("DSC").range(Cells(DSCcurrentrow + DSCcounter, Colstart), Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
With selectrange
    .Value = listie(count2)
End With

At the beginning of the macro that I open a new workbook to put all the data into, then I open and close the workbooks containing the data, then return to the new workbook that was created. There is periodic saving happening over the course of the macro.

What can I do to fix this error?

Is there a sheet named "DSC" in your workbook? Try making it: Sheets("DSC").range(Sheets("DSC").Cells(DSCcurrentrow + DSCcounter, Colstart), Sheets("DSC").Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1)) — Michael
Also, the "With actbook" isn't doing anything...you're not calling any child objects with the "." symbol in this WITH statement. I would recommend doing the same thing I posted above for your "SSC" range, namely adding Sheets("SSC").Cells to the range — Michael
btw - varibles for rows should be declared as Long since 2007 — Steven Martin
In addition to the comment below. I would personally write a few lines before your "selectrange" statement with the error. Instead of having each cells statement have so many variables and (), try to summarize the values you are trying to use into neat values. Whenever I get an error in a line with multiple references doing math in the reference, I break it out and simplify. The error will probably be known by doing that. Otherwise, use your LOCALS window to see the values of all the variables going into it. — peege
After getting cozy with the code a little. I have to wonder instead of using RANGE and setting it like that, what is the actual range you are trying to use, because it's probably a lot easier to just set a value then trying to use With selected Range and then combine all those steps. I could be wrong, and it could be that it's the only way, and I mean no insult. Perhaps you can just give an example of the actual RANGE you want it to be in, like 'Cells(row, "H")' in a comment, and we can maybe reduce what's happening. All the cells are just in a 2D XY grid, and easy to get at. — peege

Degustaf Degustaf · Accepted Answer · 2014-12-11T19:48:20

Cells will reference the ActiveSheet in the ActiveWorkbook. Those objects might not be set, and they might not be on Sheet DSC. Try this instead:

With actbook.Sheets("DSC")
    Set selectrange = range(.Cells(DSCcurrentrow + DSCcounter, Colstart), .Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
End With

Or, a more readable version might be

Set selectrange = actbook.Sheets("DSC").Cells(DSCcurrentrow + DSCcounter, Colstart)
Set selectrange = selectrange.resize(1, listlen)

Runtime Error 1004 when selecting sheet by name VBA

1 Answers