excelmultiple-columnsworksheetalternatingvba

copy all rows, paste into another worksheet alternating rows with exsiting data on destination sheet)


I am looking to copy/paste values from ws1 to ws2

Specifically, to copy a range of 2 columns of data from FROM Worksheet #1 TO Worksheet #2, pasting in alternating cells, in same column.

Example:

Worksheet 1 (from)
           Col AM   Col AN
Row 25     TEMP01   10001
Row 26     TEMP02   20002     
Worksheet 2 (to)
           Col A
Row 3      TEMP01
Row 4      10001
Row 5      TEMP02
Row 6      20002

I was just trying to get a single column to paste into alternating rows, but to no avail. Receiving a

"Object Variable or With block variable not set"

error.

Sub Alternate()
    Dim wsFrom As Worksheet
    Dim wsTo As Worksheet
    Dim LR As Long
    Dim i As Long
    Dim n As Long

    With wsFrom
        LR = wsFrom.Range("AM" & .Rows.count).End(xlUp).Row
        n = 3
        For i = 2 To LR
            wsFrom.Range("AM" & i).Copy
            wsTo.Range("A" & n).PasteSpecial xlPasteValues
            n = n + 2
        Next i
    End With
End Sub

Solution

  • You need something like:

        Set wsFrom = Sheets("WS1")
        Set wsTo = Sheets("WS2")
    

    before the:

        With wsFrom
    

    In order to define the worksheets objects.
    You also need:

        wsFrom.Range("AN" & i).Copy
        wsTo.Range("A" & n + 1).PasteSpecial xlPasteValues
    

    before:

        n = n + 2
    

    in order to copy in the alternate values. Finally, you loop starts at Row 2 and the data you've given for wsFrom starts at Row 25.