VBA Copy cells fill down on another sheet with last row

Question

Greetings,I have a spreadsheet with a tab called Clerk. It has names listed under column A. I can't use A2:A5 because the list could grow. I need all the names pasted and filled down on another tab in the same workbook called Sheet3 in column P.I need column P below to have the names fill down in order until the last row recorded on column A in that spreadsheet. For example: Jane, Jon, Doe, John, Jane, Jon, Doe etc. until it reaches the end of Sheet3.Can anyone help me with this VBA macro?Thank you!&nbsp;

djclements · Accepted Answer

kbashaw&nbsp;If I've understood everything correctly, the following code should work:&nbsp;Option Explicit

Sub RepeatClerkNames()

Dim wsSource As Worksheet, data As Variant, k As Long
    Set wsSource = Sheets("Clerk")
    data = wsSource.Range("A2:A" &amp; wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row).Value
    k = UBound(data, 1)

Dim wsOutput As Worksheet, arr() As Variant, i As Long, j As Long
    Set wsOutput = Sheets("Sheet3")
    ReDim arr(1 To wsOutput.Cells(wsOutput.Rows.Count, 1).End(xlUp).Row - 1, 1 To 1)
    j = 1
    For i = 1 To UBound(arr, 1)
        arr(i, 1) = data(j, 1)
        j = IIf(j = k, 1, j + 1)
    Next i

wsOutput.Range("P2").Resize(UBound(arr, 1)).Value = arr

End Sub&nbsp;Adjust the worksheet names, if necessary. Cheers!

Forum Discussion

VBA Copy cells fill down on another sheet with last row

Share

Resources