Copy values horizontally

[bgonen]

I have lots of values starting A1 down

Trying to copy value in A1 to the range P3:R3

Then trying to copy value A2 to the range T3:V3

(notice I need to keep a blank cell (in this case S3)

Thanks

 

[Scott Huish]

Sub test()
Dim x As Long, y As Long
y = 2
For x = 1 To Range("A" & Range("A" & Rows.Count).End(xlUp).Row) Step 2
    y = y + 1
    If Range("A" & x) = "" Then
        Range("P" & y).Resize(, 3).ClearContents
    Else
        Range("P" & y).Resize(, 3) = Range("A1").Offset(x - 1)
    End If
    
    If Range("A" & x + 1) = "" Then
        Range("T" & y).Resize(, 3).ClearContents
    Else
        Range("T" & y).Resize(, 3) = Range("A1").Offset(x)
    End If
Next x
End Sub

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("A" & i).Copy Destination:=Cells(3, 4 * i + 12).Resize(, 3)
Next i
End Sub

 

[bgonen]

Peter,
Your command works perfectly but I don't understand how column P is determined as destination,,, (trying to understand your code since actually I need to start at Q3 (not P3,,as I wrongly mentioned earlier).

Thanks bg

Hotpepper,
Thanks for your quick feedback I will try to understand and work with your commands later.

 

[Scott Huish]

Column P is Column 16

So first time through the loop 4*1+12=16
Second time throught the looop 4*2+12=20
etc.

 

[Scott Huish]

To start at Q, change the 12 to 13:

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("A" & i).Copy Destination:=Cells(3, 4 * i + 13).Resize(, 3)
Next i
End Sub

 

[bgonen]

You guys are so smart
Thanks for all the help
bg

 

[bgonen]

Sorry, I got excited too early;
when I ran the commands (below) I was hoping to change the destination (to horizontally 13 columns) but I keep getting 4 columns get copied and then the next value get copied over the next four columns.

Also, I was hoping to get one blank column (with no value),,

Sub CopyPasteAcrossColumnsFINAL()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
Range("A" & i).Copy Destination:=Cells(3, 4 * i + 13).Resize(, 13) '4*1 + 13 = Col Q
Next i
End Sub

 

[VoG]

Try

Sub CopyPasteAcrossColumnsFINAL()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    Range("A" & i).Copy Destination:=Cells(3, 14 * i + 3).Resize(, 13)
Next i
End Sub

 

[bgonen]

Peter,
I tested it and it works perfectly fine now
Thanks a lot for the quick help
bg