CODEWITHSUNDEEP
excelvba
heejin ghim
heejin ghim

Reputation: 41

Copy single cell from range macro

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

Upvotes: 1

Views: 140

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 0

Related Questions