LOOP: Copy Cells Value (in a list) from one Sheet to Another

Question

The purpose of this macro is copy one cell value (from a long list) to another cell located in a different sheet.

here's my code:

Sub journalben()

Set rawben = Sheets("BEN")
Set finaljnl = Sheets("JNL_BEN")

    Set Rng = Range("G2:G1048576")
    For Each cell In Rng
        'test if cell is empty
        If cell.Value <> "" Then

        finaljnl.Range("L4").Value = rawben.Range("G5").Value
        finaljnl.Range("K4").Value = rawben.Range("L5").Value
        End If
    Next
End Sub

With the help of the image, I will explain what I'm trying to achieve:

1. From Sheet1 ("BEN") there's a list sitting in columns G and L.
2. I will copy the cell G5 from Sheet1 and paste it in Sheet2 ("JNL_BEN") Range K4.
3. Next is I will copy the cell L5 from Sheet1 and paste it in Sheet2 ("JNL_BEN") Range L4.
4. Copy the next in line and do the same process just like No.2 and 3 but this time, it will adjust 1 row below.
5. Copy the whole list. That means up to the bottom. The list is dynamic, sometimes it will go for 5,000 rows.

For some reasons, copying the entire column is not an option to this macro due to requirement that cells from sheet1 MUST be pasted or placed in Sheet2 from left to right (or horizontally).

I hope you could spare some time to help me. My code didn't work, I guess the implementation of FOR EACH is not correct. I'm not sure if FOR EACH is the best code to use.

I appreciate anyone's help on this. Thank you very much! May the force be with you.

Answer 1

Try this:

Sub journalben()
    Dim i As Long, lastRow As Long

    Set rawben = Sheets("BEN")
    Set finaljnl = Sheets("JNL_BEN")

    lastRow = rawben.Cells(Rows.Count, "G").End(xlUp).Row

    For i = 5 To lastRow
        'test if cell is empty
        If rawben.Range("G" & i).Value <> "" Then
            finaljnl.Range("K" & i - 1).Value = rawben.Range("G" & i).Value
            finaljnl.Range("L" & i - 1).Value = rawben.Range("L" & i).Value
        End If
    Next i
End Sub

I am starting FOR from 5 as the data in your image starts from cell G5 (not considering the header).

Answer 2

It'll be easier to use a numeric variable for this :

Sub journalben()

Set rawben = Sheets("BEN")
Set finaljnl = Sheets("JNL_BEN")

    Set Rng = rawben.Range("G4:G1048576")
    For i = Rng.Cells(1,1).Row to Rng.Cells(1,1).End(xlDown).Row
        'test if cell is empty
        If rawben.Range("G" & i).Value <> vbNullString Then
            finaljnl.Range("L" & i - 1).Value = rawben.Range("G" & i).Value
            finaljnl.Range("K" & i - 1).Value = rawben.Range("L" & i).Value
        End If
    Next i
End Sub

Answer 3

You should use a simple for loop. It is easier to work with.

Also, to have it dynamic and to go to the last cell in the range, use the SpecialCells method.

And your range needs to be set correctly from row 5.

Here is the code:

Sub journalben()

Set rawben = Sheets("BEN")
Set finaljnl = Sheets("JNL_BEN")

    Set Rng = Range("G5:G1048576")
    For i = Rng.Cells(1,1).Row to Rng.SpecialCells(xlCellTypeLastCell).Row
        If rawben.Range("G" & i).Value <> vbNullString Then
            finaljnl.Range("L" & CStr(i - 1)).Value = rawben.Range("G" & CStr(i)).Value
            finaljnl.Range("K" & CStr(i - 1)).Value = rawben.Range("L" & CStr(i)).Value
        End If
    Next i
End Sub