Solved: Copy and Paste to new row/column IF values exist in paste

[canvcsixteen]

A novice to VBA -- I have exhausted my few resources (two beginner texts!) and now resorting to the experts.

I am want to copy and paste a range of cell to another to sheet (which I know how to do), however IF the there are values in the targeted paste cells, than paste to a new column or the next empty column to the right.

Is this difficult? Can anyone help?

[mdmackillop]

Welcome to VBAX

[VBA]Option Explicit

Sub Test()
Dim r As Range
Dim cel As Range
Dim tgt As Range

Set r = Sheets(1).Range("A1:A10")

For Each cel In r
Set tgt = Sheets(2).Cells(cel.Row, Columns.Count).End(xlToLeft)
If tgt <> "" Then Set tgt = tgt.Offset(, 1)
cel.Copy tgt
Next
End Sub[/VBA]

[canvcsixteen]

Amazing!!

Thank you kindly.