CODEWITHSUNDEEP
excelvbaoffsetpaste
nicysch
nicysch

Reputation: 11

How to copy data into every other cell in a row?

I copy cells (B18:B23) from Sheet1 to Sheet2 while transposing.

I want to paste to every other column (O4,Q4,S4, etc. skipping a cell between).

Sub CopyData()
    Dim wsSrc As Worksheet
    Dim wsDst As Worksheet
    
    Set wsSrc = Worksheets("Sheet1")
    Set wsDst = Worksheets("Sheet2")
        
    wsSrc.Range("B18:B23").Copy
    wsDst.Offset(1, 1).Range("O4").PasteSpecial xlPasteValues, Transpose:=True
       
    Application.CutCopyMode = False
End Sub

I've gotten it to transpose. How do I skip every other column when it pastes?

Upvotes: 1

Views: 879

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

There's not a "skip/transpose" using the standard Copy/Paste method, so I think the best option would be to loop through your values and use an offset. This should do it...

Sub CopyData()
    Dim wsSrc As Worksheet, wsDst As Worksheet, aCell As Range, tOff As Long
    
    Set wsSrc = Worksheets("Sheet1")
    Set wsDst = Worksheets("Sheet2")
    
        For Each aCell In wsSrc.Range("B18:B23").Cells
            wsDst.Range("o4").Offset(0, tOff).Value = aCell.Value
            tOff = tOff + 2

        Next aCell

End Sub

Upvotes: 2

Related Questions