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
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.