Gfrktyl

I have a list of names in excel,

For example

Lizzy

William

Kate

Charles

I would like to use some VBA code (or other method to repeat this 12 times).

I will be doing this over and over again and sometimes my list of names may include less or more.

Has anyone got any direction for me as I am really stuck.

The output I would like in this specific case is,

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

Lizzy

William

Kate

Charles

What I have tried:

I have found a method using formulas, see, https://www.excel-bytes.com/how-to-repeat-a-range-of-items-multiple-times-in-excel/

However the problem with this is the indexing the initial list as my list will change.
Link to question put on hold: Copy and paste a list of names in excel 12 times

This question was originally asked by another user and put on hold so
I couldn't answer. I have a solution so I have reasked the question
and I will provide an answer and tag the original asker.

For any list of names in the first column of an excel spreadsheet, this VBA code will repeat based on the entry in cell C2, therefore, if you want the list repeated 10 times and there are 10 names in the original list, insert a 110 in the original list.

    Option Explicit



Sub LRow()



    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")  '<=== Edit Sheet Name

    Dim LRow As Long



    LRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row



    ws.Range("A1:A" & LRow).Select



End Sub

Is the first but of code that you need and it essentially highlights anything in the A column.

The second part of the code drags down the list so that it is repeated.

Add them both in and run the second as a macro.

I hope this helps.

    Sub Master()

'

' Master Macro

'



'

    LRow

    Selection.AutoFill Destination:=Range("A1:A" & Range("C2").Value), Type:=xlFillDefault

    Range("A1:A" & Range("C2").Value).Select

    Range("B1").Select

End Sub