Forum Discussion

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 06, 2023

patriciaarnolderg 

You can create a simple VBA macro in Excel to populate a name in cell A4 as many times as the value in B4 on a different worksheet.

Here is a step-by-step guide on how to do it:

  1. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
  2. In the VBA editor, insert a new module by clicking "Insert" > "Module."
  3. Copy and paste the following VBA code into the module:

vba code:

Sub PopulateNames()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim NameToPopulate As String
    Dim CountToPopulate As Long
    Dim i As Long
    
    ' Set the source worksheet
    Set wsSource = ThisWorkbook.Worksheets("SourceSheet") ' Replace "SourceSheet" with the name of your source worksheet
    
    ' Set the destination worksheet
    Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet") ' Replace "DestinationSheet" with the name of your destination worksheet
    
    ' Get the name to populate from cell A4 of the source worksheet
    NameToPopulate = wsSource.Range("A4").Value
    
    ' Get the count from cell B4 of the source worksheet
    CountToPopulate = wsSource.Range("B4").Value
    
    ' Clear the destination worksheet
    wsDestination.Cells.Clear
    
    ' Populate the name in the destination worksheet B4 cell as many times as specified
    For i = 1 To CountToPopulate
        wsDestination.Cells(i, 1).Value = NameToPopulate
    Next i
End Sub
  1. Modify the code to fit your specific worksheet names. Replace "SourceSheet" and "DestinationSheet" with the names of your source and destination worksheets.
  2. Close the VBA editor.
  3. To run the macro, press ALT + F8, select "PopulateNames," and click "Run."

This VBA macro will clear the destination worksheet and then populate the name from cell A4 of the source worksheet as many times as specified in cell B4 of the source worksheet on the destination worksheet. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

patriciaarnolderg's avatar
patriciaarnolderg
Copper Contributor
Nov 17, 2023

This worked great! how do i get it to repeat?  I have values from A4 to A50
  • djclements's avatar
    djclements
    Silver Contributor
    Nov 18, 2023

    patriciaarnolderg An appropriate VBA solution will depend on which scenario applies to your situation. Please clarify the following:

     

    1) Are the number of repeats the same for each item in range A4:A50, based on the number in cell B4?

    - OR -

    2) Does the range B4:B50 contain a list of numbers indicating a specific number of repeats for each corresponding item in range A4:A50?

     

    Incidentally, if you are using MS365, this can also be accomplished with formulas for each scenario as follows:

     

    Scenario 1: the number of repeats is set in cell B4

     

    =TOCOL(IF(SEQUENCE(, B4), A4:A50))

     

    Scenario 2: the number of specific repeats for each individual item is set in range B4:B50

     

    =LET(arr, A4:B50,
DROP(REDUCE(0, SEQUENCE(ROWS(arr)), LAMBDA(v,n,
   VSTACK(v, IF(SEQUENCE(INDEX(arr, n, 2)), INDEX(arr, n, 1))))), 1))

     

    Note: also include the sheet name in the range references shown above, if you are returning the results to a different worksheet (ie: Sheet1!B4 and Sheet1!A4:A50, or Sheet1!A4:B50).

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 20, 2023

        patriciaarnolderg 

        Sub list()

Dim i, j, k As Long

Range("C:C").Clear

For i = 4 To 50
For j = 1 To Cells(i, 2)

Cells(k + i, 3).Value = Cells(i, 1).Value
k = k + 1

Next j
k = k - 1

Next i

End Sub

        For Scenario 2 described by djclements you can use this macro as well.

Resources