VBA one list to another

[Cantonalives]

Hi,
I would like to populate a column with values from a list on a different sheet.


Sheet 2, A1-A55 could contain names. But could contain blanks.

So on Sheet1, A1-a55 - I need the names filled down if not blank. This would be simple with a formula, but I need to the cells to be free from formula and to also have data validation from another list if blank.


Any help much appreciated as I am sure this is simple. VBA sadly not my speciality.

Also need it to trigger upon update on sheet2.

Thank you...


-CL

 

[Cantonalives]

Please see dummy file in the hope someone can help...

Ta.

 

[Cantonalives]

Anyone pls?

 

[Cantonalives]

This is what I have so far. This copies data from column P on same sheet. But duplicates the first row into 2 new rows. So the cell P1 is written to B7 and B8. Nothing lower than that. As you can see, I am no VBA coder.

Option Explicit

Sub replicate()
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim rw As Long, col As Long

    Set ws = ThisWorkbook.Sheets("Timesheet")


    rw = 7: col = 2
   

    With ws
             
       

        For i = 1 To 77

            For j = 16 To 16
           
       
                If .Cells(i, j).Value <> "" Then

                    .Cells(rw, col).Value = .Cells(i, 1).Value
 
                    .Cells(rw, col + 0).Value = .Cells(1, j).Value
                  rw = rw + 1
                End If
            Next j
        Next i
    End With
End Sub

 

[Marc L]

Hi !

So in your real worksheet row #1 is always blank ?

If it's not the case, join a sample workbook reflecting exactly
the layout of working workbook with data according to your initial post …
​