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:

From Sheet1 ("BEN") there's a list sitting in columns G and L.
I will copy the cell G5 from Sheet1 and paste it in Sheet2 ("JNL_BEN") Range K4.
Next is I will copy the cell L5 from Sheet1 and paste it in Sheet2 ("JNL_BEN") Range L4.
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.
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.

Mrig · Accepted Answer · 2016-05-30T07:24:19.943

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).

R3uK · Answer 2 · 2016-05-30T07:26:07.940

1

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

edited May 30 '16 at 07:26

answered May 30 '16 at 07:13

R3uK

14,417
7
43
77

Hi @R3uK, Thanks for your help. I'm not sure what's wrong but when I ran your code, nothing is happening. Not sure if the "Set Rng = Range("G2:G1048576")" is correct. I tried deleting this line but It gave me an error. – Ben Daggers May 30 '16 at 07:17
@BenDaggers The reason nothing is happening is because you start with this code on row 2. See my similar answer below. – ib11 May 30 '16 at 07:26
1

@BenDaggers : My bad, I didn't pay attention but your range start at row 4 and `xlDown` couldn't work like this. I edited the range (take notice that it is far better to precise the Sheet object you are talking about, if not it'll take active sheet). Take a look at the solution of @Mrig, using `xlUp` which would be more safe to find last row if the data weren't continuous! – R3uK May 30 '16 at 07:29
@BenDaggers One more point to the `xlDown` and `xlUp` @R3uK wrote of. These stop at empty cells. You seem to be testing for empty that implies that you do have empty cells, which will not allow the loop to go though to the end. In my answer I recommend the `SpecialCells` which gives you the absolute last cell to loop until. – ib11 May 30 '16 at 07:33
1

Totally agree. Didnt noticed that. Thanknu very much for your efforts. – Ben Daggers May 30 '16 at 07:41
@ib11 - Did you try `lastRow = rawben.Cells(Rows.Count, "G").End(xlUp).Row` with blank cells. I don't think so. `xlDown` and `xlUp` might stop at empty cells but `.End(xlUp).Row` returns the bottom most cell in a column in the worksheet. That means @BenDaggers will get his desired result using `.End(xlUp).Row`. – Mrig May 31 '16 at 04:57
@Mrig Yes I did and I know, my comment on this was more like of a general note, since `xlUp` and `xlDown` find the next empty or non-empty cell. I did *not* mean that this one would not work in locating the last row: `lastRow = rawben.Cells(Rows.Count, "G").End(xlUp).Row` as it certainly does! @Mrig's solution is simple and good. – ib11 May 31 '16 at 05:06
1

@ib11 - Still I'll recommend not to use 'Rng.SpecialCells(xlCellTypeLastCell).Row' because if you delete cells, the '.SpecialCells(xlCellTypeLastCell)' is only recalculated when the sheet is re-saved See this [link](http://stackoverflow.com/questions/25110873/excel-application-cells-specialcellsxlcelltypelastcell-returning-bottom-of-wor/25126427#25126427). – Mrig May 31 '16 at 05:21
@Mrig Thanks for the info. Yes, I know this as well and it is clear. I am not insisting on it. I am just saying that if you test for the empty cells and you know they are empty then there is no harm. We are not returning here an absolute last used cell. – ib11 May 31 '16 at 05:34

score 0 · Answer 3 · answered May 30 '16 at 07:24

0

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

answered May 30 '16 at 07:24

ib11

2,530
3
22
55

`xlCellTypeLastCell` is not a good solution to find the last data cell in a sheet, see : http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – R3uK May 30 '16 at 07:31
I could use `xlUp` of course, but since already testing for empty cells even if the last cells are deleted, and so the loop continues because of `xlCellTypeLastCell`, they still will be ignored because of the `If`. So this code is still workable. Unreasonable to downvote therefore on the contrary of the unworkable code with `xlDown` from the top which you corrected since. – ib11 May 31 '16 at 05:38

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

3 Answers3