Select non-contiguous cells and post to specific columns on next blank row of sheet

[S M Morrow]

Hi, I need to select non-contiguous cells in a sheet and paste them to specific columns on the next blank row of another sheet. The code below can copy non-contiguous cells and paste to specific cells on the required sheet, but I cannot adapt to get it to copy to the next blank row.

Sub Copycell()

Dim rng1 As Range
Set rng1 = Range("B2,B4,B6")

Dim rng2 As Range
Set rng2 = Sheets("list").Range("A2,B2,D2")

Dim i As Long
For Each cel In rng2
cel.Value = rng1.Cells(i + 1)
i = i + 1
Next

End Sub

 

[Joe4]

Welcome to the Board!

Rather than trying to figure out the details from errant code, can you explain the exact ranges you are wanting to copy from and to, and how they change, i.e.
Are you always copying from the same range?
What is the the first row you are copying to?
Your one sheet is named "list". What is the other named?

 

[MickG]

This may help:-
NB:- Your code is using the Index value from a range, and when your range is non-contiguous the Index number is not always the next value.

[COLOR="Navy"]Sub[/COLOR] MG15May41
[COLOR="Navy"]Dim[/COLOR] rng1 [COLOR="Navy"]As[/COLOR] Range, Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rng2 [COLOR="Navy"]As[/COLOR] Range, i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cel [COLOR="Navy"]As[/COLOR] Range [COLOR="Navy"]With[/COLOR] Sheets("List")
    [COLOR="Navy"]Set[/COLOR] rng2 = .Range("A2,B2,D2")
    Lst = .Range("A" & Rows.Count).End(xlUp).Row
 [COLOR="Navy"]End[/COLOR] With
 
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cel [COLOR="Navy"]In[/COLOR] rng2
    cel(Lst).Value = Range(Array("B2", "B4", "B6")(i))
    i = i + 1
 [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick