Range(cells(x,y)).copy Throwing Error 1004

Question

I have a worksheet that I have to export daily that consists of work schedules. What I am trying to do is manipulate the data in the .csv file before copying it over to my workbook. For any given day the tech could have up to 4 schedules for 1 day. I am trying to find the column that the Regular shift is in and move it to column E.

| Supervisor | Technician | On Duty? | Earliest Route Time | Shift 1 Type | Shift 1 Start | Shift 1 End | Shift 2 Type | Shift 2 Start | Shift 2 End |
|------------|------------|----------|---------------------|--------------|---------------|-------------|--------------|---------------|-------------|
| Harold     | Doug       | No       |                     | Meetings     | 8:00 AM       | 9:30 AM     | Regular      | 9:30 AM       | 4:30 PM     |
| Harold     | Greg       | No       |                     | Meetings     | 8:00 AM       | 9:30 AM     | Regular      | 9:00 AM       | 4:30 PM     |
|            |            |          |                     |              |               |             |              |               |             |

I have tried to implement the solution from (Why does Range work, but not Cells?) there was another one but I seem to have lost it.

    Sub test_cell()
    Dim sh1 As Worksheet
    Dim x as Integer
    Dim col as Integer

    For Each w In Workbooks  'loop through open workbooks
        If w.Name = "tech_shifts_now.csv" Then
            w.Activate

            Sheets("tech_shifts_now").Select
            Set sh1 = ActiveWorkbook.Sheets("tech_shifts_now")

            x = 3
            If Cells(x, 5) <> "Regular" Then
                With sh1
                    .Range(.Cells(x, 5), .Cells(x, 7)).Copy Destination:=.Range(.Cells(x, 17))  'Move current data to Q
                End With
                'Range("E" & x & ":G" & x).Copy Range("Q" & x)

                'Find the column that regular shift is in
                Rows(x).Find(What:="Regular", LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate

                'get the columns number
                col = ActiveCell.Column

                'copy the data for regular to Column E
                Range(Cells(x, col), Cells(x, col + 2)).Copy Destination:=Range(Cells(x, 5))

                'Copy for Column Q to where we just removed the Regular data from
                Range("Q" & x & ":S" & x).Copy Range(Cells(x, col))
            End If
        End If
    Next w
    End Sub

I am receiving error 1004 Method Range of Object _worksheet failed when it gets to .Range(.Cells(x, 5), .Cells(x, 7)).Copy Destination:=.Range(.Cells(x, 17))

Your destination is wrong. It should just be `.Cells(x, 17)`. Or, it `.Range(.Cells(x, 17).Address)`. But the former would be preferable IMO. — David Zemens, Jun 25 '19 at 17:08
Thank you. I knew it was something little I was missing.. Works like a charm. I appreciate the quick reply — Kavorka, Jun 25 '19 at 18:16
... Do you want to put your response as an answer so i can mark it complete? Or should I just update the code with your answer and mark it complete? — Kavorka, Jun 25 '19 at 18:44
Sure, and I added a bunch of detail explaining *why* it doesn't work that way. Cheers. — David Zemens, Jun 25 '19 at 19:22

score 1 · Accepted Answer · answered Jun 25 '19 at 19:22

Your destination is wrong. It should just be .Cells(x, 17). Or, .Range(.Cells(x, 17).Address).

Range(Cells(x, col), Cells(x, col + 2)).Copy Destination:=.Cells(x, 17)

Why? The Range object has at least two constructors which aren't explicitly called out as such. If you call Range with a single parameter, it's assumed to be this (emphasis added):

Use Range(arg), where arg names the range, to return a Range object that represents a single cell or a range of cells

Here, Range(<something>) expects a string that identifies a range, like Range("A2") or Range("Some_Named_Range"), etc.

When you pass instead Range(Cells(1,1)), the inner part (Cells(1,1), which is itself a Range) is evaluated. Since a single-cell Range can (usually) be implicitly converted to a String from its Value property, but since this is not (usually) a valid reference, the error occurs.

As mentioned, you could do: Range(Cells(1,1).Address) but I think that's kind of ugly, even if valid.

Range does have a constructor that accepts TWO Range arguments, but you were passing a Range object to the constructor which expects ONE String argument. Implicit conversion happens under-the-hood, and you're left with a hard-to-troubleshoot error :)

Use Range(cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object

Following this logic, you actually could do Range(Cells(1,1), Cells(1,1)), but again, needlessly verbose/repetitive and I wouldn't prefer this convention.

Range(cells(x,y)).copy Throwing Error 1004

1 Answers1