Paste contiguous list into non-contiguous cells.

[jordanchenier14]

I have list that is current organized where all of the cells are directly on top of one another and I would like to paste the values into another spreadsheet, but only paste into specific cells. In the attached image I'd like to be able to take list A and then paste it into only blank cells of the next column. It's not a simple merge though, as the values don't align with the new paste area. Thank you in advance.

blank.xlsx
	A	B	C	D	E
1	List A		Destination		Desired Outcome
2
3	1		0		0
4	2				1
5	3		0		0
6	4		0		0
7	5				2
8	6				3
9	7		0		0
10	8				4
11	9		0		0
12	10				5
13			0		0
14			0		0
15			0		0
16			0		0
17					6
18			0		0
19					7
20			0		0
21					8
22			0		0
23					9
24			0		0
25					10
26			0		0
Sheet1

 

[bebo021999]

What d you refer to, VBA or formula?
Paste to column E, or rewrite collumn C?
What columns in sheet1, and what columns in destination sheet?

 

[jordanchenier14]

I want to take column A, paste it into column C so that when it's finished it looks like column E.

 

[bebo021999]

Is list A actual sequence numbers from 1?

 

[jordanchenier14]

It's an example. I just want to preserve the value and the order when it pastes into the empty cells of the desired column.

 

[bebo021999]

Try:

Option Explicit
Sub test()
Dim LrA&, LrC&, k&, cell As Range
With WorksheetFunction
LrA = Cells(Rows.Count, "A").End(xlUp).Row
LrC = LrA - .CountBlank(Range("C3:C" & Cells(Rows.Count, "C").End(xlUp).Row)) + Cells(Rows.Count, "C").End(xlUp).Row - 2
    For Each cell In Range("C3:C" & LrC)
        If cell = "" Then
            k = k + 1
            cell.Value = .Index(Range("A3:A" & LrA), k)
        End If
    Next
End With
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

Sub Test()
  Dim R As Long, ListA As Variant, Blank As Range
  ListA = Range("A3", Range("A3").End(xlDown))
  On Error GoTo NoMoreData
  For Each Blank In Range("C3:C" & Rows.Count).SpecialCells(xlBlanks)
    R = R + 1
    Blank = ListA(R, 1)
  Next
NoMoreData:
End Sub