copy data from each cell in range loop and paste it on another sheet

Question

I have below vba code Sub Macro1()

    Dim FLrange As Range
    Set FLrange = Range("C3:E3")

    For Each cell In FLrange
        If cell.Value = 0 Then cell.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"

    Next cell

End Sub

Now, before executing the formula in above code, i would like to add another function on this. I need to copy the current cell value to another sheet on exactly same cell. ie, value from sheet1 cell C3 has to be pasted sheet2 cell C3

I have tried with

Sub Macro1()

    Dim FLrange As Range
    Set FLrange = Range("C3:E3")

    For Each cell In FLrange
    Selection.Copy
    Sheets("sheet2").Paste
        If cell.Value = 0 Then cell.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"

    Next cell

End Sub

But this one not pasting the value corresponding cell in sheet2, but see it is pasting formula to random cell.

How can I paste the value from each cell in range in current sheet (sheet1), to corresponding cell in another sheet shet2

Ben Black Ben Black · Accepted Answer · 2014-12-10T15:26:54

First off, you're using Selection in your code, but you're never changing your ActiveCell. Regardless, using ActiveCell is bad practice, you should always use object references to copy and not your selection unless it's completely necessary.

Secondly you never give it an actual range to paste to, you just give it the sheet, so I'm surprised this isn't throwing an error of some sort when you try to run it.

What my code does it sets the range you want to iterate on (to use Me you need to place this in the Sheet Module of Sheet1, otherwise you need to explicitly say it's on Sheet1), iterates over it and then copies it to the same Col/Row index on a second explicitly defined sheet.

When it comes to best practices you should always explicitly define things, especially when you're talking about VBA.

Option Explicit
Sub Test()

    Dim rng         As Range
    Dim c           As Range
    Dim dest        As Worksheet


    Set rng = Me.Range("A1:A10")
    Set dest = ThisWorkbook.Worksheets("Sheet2") '' rename this to whatever sheet you want this to copy to

    For Each c In rng
        c.Copy dest.Cells(c.Row, c.Column)

        If c.Value = 0 Then
            c.FormulaR1C1 = "=SUMIFS(raw!C4,raw!C1,R[0]C1,raw!C3,R2C[0])"
        End If
    Next c

End Sub

copy data from each cell in range loop and paste it on another sheet

1 Answers