PDA

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



canvcsixteen
04-24-2013, 11:23 AM
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
04-24-2013, 12:27 PM
Welcome to VBAX

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

canvcsixteen
04-24-2013, 02:42 PM
Amazing!!

Thank you kindly.