VBA: Copying Values Run Time Error 1004

Question

I'm trying to copy a range in the sixth sheet to the first or third sheet depending on a substring value. I'm getting a "Run-time error '1004'" when I try using this code. I tried finding a solution--the only possible answer I could find was adding a ThisWorkbook.Save, but that did not change the result.

Dim LastRow As Long

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
End With

'Iterates through rows in column A, and copies the row into proper sheet depending on "H" or "L"
For i = 4 To LastRow
    If InStr(1, Range("A" & i), "L") <> 0 Then
        ThisWorkbook.Worksheets(1).Range(Cells(i, 9), Cells(i, 48)).Value = ThisWorkbook.Worksheets(6).Range(Cells(i, 1), Cells(i, 40)).Value
    ElseIf InStr(1, Range("A" & i), "H") <> 0 Then
        ThisWorkbook.Worksheets(3).Range(Cells(i, 9), Cells(i, 48)).Value = ThisWorkbook.Worksheets(6).Range(Cells(i, 1), Cells(i, 40)).Value
    End If
Next i

'Message Box when tasks are completed
    MsgBox "Complete"

When the debugger is launched after the error, it specifically highlights the ElseIfresult:

ThisWorkbook.Worksheets(3).Range(Cells(i, 9), Cells(i, 48)).Value = ThisWorkbook.Worksheets(6).Range(Cells(i, 1), Cells(i, 40)).Value

Edit

Thanks to the help from those who answered below, I was able to solve the issue. I removed the .Cells from .Range and referenced the cells I wanted through .Range("I" & y1 & ":AV" & y1) (first example of the change). Additionally changed the way I cited my worksheets, as I defined them as the variables wsR, wsL, and wsH. Also added in the With / End With statements.

Private Sub CommandButton2_Click()


Dim LastRow As Long
Dim wsR As Worksheet
Dim wsL As Worksheet
Dim wsH As Worksheet
Dim y1 As Integer
Dim y2 As Integer

'Set row value for light chain
y1 = 4
'Set row value for heavy chain
y2 = 4

'Set raw data worksheet
Set wsR = ThisWorkbook.Worksheets(6)
'Set light chain worksheet
Set wsL = ThisWorkbook.Worksheets(1)
'Set heavy chain worksheet
Set wsH = ThisWorkbook.Worksheets(3)

'Finds last row
With wsR
    LastRow = .Cells(.Rows.count, "A").End(xlUp).Row
End With

'Iterates through rows in column A, and copies the row into proper sheet depending on "H" or "L"
For i = 4 To LastRow
    'Checks for "L" for light chain
    If InStr(1, Range("A" & i), "L") <> 0 Then
        With wsL
            .Range("I" & y1 & ":AV" & y1).Value = wsR.Range("A" & i & ":AN" & i).Value
        End With
        y1 = y1 + 1
    'Checks for "H" for heavy chain
    ElseIf InStr(1, Range("A" & i), "H") <> 0 Then
        With wsH
            .Range("I" & y2 & ":AV" & y2).Value = wsR.Range("A" & i & ":AN" & i).Value
        End With
        y2 = y2 + 1
    End If
Next i

'Message Box when tasks are completed
    MsgBox "Complete"


End Sub

See [Is the . in .Range necessary when defined by .Cells](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) for an expanded discussion on this common problem. — , May 22 '17 at 18:43

score 4 · Answer 1 · answered May 22 '17 at 18:21

This is a very common issue - you have not added a sheet reference to your Cells (and not all your Ranges are qualified either which you should remedy).

Sub x()

Dim LastRow As Long, ws As Worksheet

Set ws = ThisWorkbook.Worksheets(6)

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Iterates through rows in column A, and copies the row into proper sheet depending on "H" or "L"
For i = 4 To LastRow
    If InStr(1, Range("A" & i), "L") <> 0 Then
        With ThisWorkbook.Worksheets(1)
            .Range(.Cells(i, 9), .Cells(i, 48)).Value = ws.Range(ws.Cells(i, 1), ws.Cells(i, 40)).Value
        End With
    ElseIf InStr(1, Range("A" & i), "H") <> 0 Then
        With ThisWorkbook.Worksheets(3)
            .Range(.Cells(i, 9), .Cells(i, 48)).Value = ws.Range(ws.Cells(i, 1), ws.Cells(i, 40)).Value
        End With
    End If
Next i

'Message Box when tasks are completed
    MsgBox "Complete"

End Sub

As in there is a sheet reference. You had done it partially with `ThisWorkbook.Worksheets(1).Range` but if omitted the code will assume you mean the active sheet so your range ends up being composed of cells in different sheets, hence the error. Note that I have added a sheet reference to every instance of Range/Cells (except those where I am not sure which is the relevant sheet). — SJR, May 22 '17 at 18:37

score 2 · Answer 2 · answered May 22 '17 at 18:43

99% of the time the 1004 error is because you have sloppily defined range objects.

ThisWorkbook.Worksheets(3).Range(Cells(i, 9), Cells(i, 48)).Value  = _
    ThisWorkbook.Worksheets(6).Range(Cells(i, 1), Cells(i, 40)).Value

Both the left- and right-hand side of this assignment statement starts out as a qualified range (part of Worksheets(3) and Worksheets(6), respectively), but the dynamic assignment using Cells method is causing a failure, because Cells as part of the hidden _Globals namespace, refers to ActiveSheet.Cells.

This statement will fail no matter what sheet is active, because one or the other side of that statement can't be evaluated.

Did not realize that about `.Cells`. Replaced them all by setting up my ranges as `.Range("A" & i & ":AN" & i)`. Thank you for the help. — MTJ, May 22 '17 at 19:00

VBA: Copying Values Run Time Error 1004

2 Answers2