Loop to Repeat Names

Question

Dear community,

I believe it is simple the most solution, I am trying to make the following rule of business:

I have a list of names on a Plan1, I need the name of each of them to be repeated by 12x each in Plan2, I have a code that can repeat the first name, but I’m not able to believe my value.

Sub RODAR()


Dim num As Integer
Dim wb As Workbook
Dim ws As Worksheet
Dim wss As Worksheet
Dim nome As String

Set wb = ActiveWorkbook
Set ws = Sheets("Plan1")
Set wss = Sheets("Plan3")

nome = 1

For cont = 2 To 13

wss.Select
nome = Cells(2, 2)

ws.Select
Cells(cont, 2).Value = nome

nome = nome

Next

Answer 1

You’d need to step up your loop with some information, follows a small model I made (I could not test...) but to see the logic:

Dim i As Integer
Dim j As Integer
Dim wsORIGEM As Worksheet
Dim wsDESTINO As Worksheet
Dim NOMES As Variant
Dim NOME As Variant

Set wsORIGEM = Sheets("Plan1")
Set wsDESTINO = Sheets("Plan2")

NOMES = wsORIGEM.Range("A1:A3").Value

For Each NOME In NOMES
    j = j + 1
    For i = 1 To 12
        If j = 1 Then
            wsDESTINO.Cells(i, 1).Value = NOME
        Else
            wsDESTINO.Cells(i + (12 * (j - 1)), 1).Value = NOME
        End If
    Next
Next

I think I can clean this one up real good loop and those variables, however, I did on top of your code only to work, but I confess that it was very "ugly" my code there... sorry for that.

Answer 2

Thank you very much Evert,

Following solution of the question raised above:

Remembering that I had as much help from Mr. Evert as I did from Mr. Anderson

Sub RODAR()
    
    Dim num As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wss As Worksheet
    Dim nome As String
    Dim linha As Long
    Dim linha2 As Long
    
    
    Set wb = ActiveWorkbook
    Set ws = Sheets("Plan1")
    Set wss = Sheets("Plan3")
    
    
    nome = 1
    linha = 2
    linha2 = 2
    
    While wss.Cells(linha, 2) <> ""
    While ws.Cells(linha2, 2) <> ""
    linha2 = linha2 + 1
    Wend
        For cont = linha2 To linha2 + 11
        nome = wss.Cells(linha, 2)
        ws.Cells(cont, 2).Value = nome
        Next cont
        
        linha = linha + 1
    Wend
    
End Sub