Copy single cell from range macro

Question

I have data on Sheet1 which varies in quantity. And I need a macro to loop through all the range if if the cell has a value, then copy it to a specific column on Sheet2. I cannot simply copy the whole range for reasons I won't get into. I need to loop through each cell one by one and paste the value in a specific format on Sheet2.

I have the below code, which gives me

Error '1004' Method 'Range of Object'_Worksheet' failed.

Sub COPY()

Dim i As Long, j As Long, rng As Range, k As Long, ws1 As Worksheet, ws2 As Worksheet
k = 1
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Set rng = Range("B4:D17")
For i = 1 To rng.Rows.Count

If Not IsEmpty(rng.Cells(i, 1)) Then
ws1.Range(rng.Cells(i, 3)).Copy Worksheets("Sheet2").Range("A1")

End If
Next

End Sub

Your rng variable is referencing the active worksheet because you did not qualify your Range object when you set it. — braX
@braX I tried updating my set to: Set rng =Worksheets("Sheet1").Range("B4:D17") still same error — heejin ghim
What's going on with ws1.Range(rng.Cells(i, 3))? rng.Cells() already returns a range object, so why are you placing this into another function that does essentially the same thing? — K.Dᴀᴠɪs
@K.Dᴀᴠɪs I need the macro to loop through a range. Then when it comes across a cell with a value. I need it to copy a value in the next column to the next empty cell on another sheet. I think I need to ditch the range. — heejin ghim
@K.Dᴀᴠɪs I used ws1/ws2 initially but when troubleshooting I used the full sheet name. — heejin ghim

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2020-02-05T00:04:14

Forget the rng object altogether. It would be more necessary in a For Each statement, but you are simply looping through rows in your sheet.

Create a simple For i statement that just loops the rows you need:

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

Dim i As Long
For i = 4 To 17
    If Not IsEmpty(ws1.Cells(i, 2)) Then
        ws1.Cells(i, 4).Copy ws2.Range("A1")
    End If
Next i

As I've already pointed out, your copy destination ws2.Range("A1") is static, so everytime your criteria is met it continues to overwrite your data here. But you seem to have that handled so I won't assume what you are trying to do with it.

Copy single cell from range macro

1 Answers