As far as the logic of the copying of values, I think this is something you need to work out. But I do have some hints of how to get there.
Do NOT use Select/Copy/Paste - Use direct assignment instead
Range("G5").Resize(3, 1).Value = Range("F5").Value
This will take the one value in cell F5 and use it to assign the three cells G5:G7.
To pick a specific value in a table, use the .Cells() function
For i=1 to 10
Range("B2").Cells(i,1).Value = Range("A2").Cells(i,1).Value
Next i
To count the number of rows down, or column across that have values use the following functions (placed in a module).
Public Function CountCols(ByVal r As Range) As Long
If IsEmpty(r) Then
CountCols = 0
ElseIf IsEmpty(r.Offset(0, 1)) Then
CountCols = 1
Else
CountCols = r.Worksheet.Range(r, r.End(xlToRight)).Columns.Count
End If
End Function
Public Function CountRows(ByVal r As Range) As Long
If IsEmpty(r) Then
CountRows = 0
ElseIf IsEmpty(r.Offset(1, 0)) Then
CountRows = 1
Else
CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
End If
End Function
To be used as
Dim n as Long
' Count rows in table starting from A2
n = CountRows(Range("A2"))
Dim r as Range
' Set a range reference to n×1 cells under A2
Set r = Range("A2").Resize(n,1)
To check if a cell is empty, use IsEmpty() just like I have used in CountRows().
Can you piece all these pieces together to make a macro that does what you want? You should: a) check how many rows are in the table, b) go down the cells and check if they are empty in the destination and c) if they are, copy from the same row in the source.
