Results 1 to 4 of 4

Thread: creating new cells based on cell value in separate sheet

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default creating new cells based on cell value in separate sheet

    Good morning ... I'm not sure if this can be done, or even how exactly to describe it, but here goes ...

    I need to create a "ballot" file, which I will then refer to after generating a random number using a formula.

    I have a source spreadsheet with names and the # of ballots each name gets, what I don't know how to do is to generate a separate worksheet or workbook, which will then put each name in a cell in column A the appropriate number of times.

    For example, if the source workbook is:

    A B
    1 John 2
    2 Jane 5
    3 Steve 3







    ... the new worksheet/workbook would look like this:

    A
    1 John
    2 John
    3 Jane
    4 Jane
    5 Jane
    6 Jane
    7 Jane
    8 Steve
    9 Steve
    10 Steve

















    Does anyone know how to accomplish this, or even point me in the right direction?

    Much thanks in advance for any assistance!

    PS - Not sure why there are so many blank lines after the 2nd table, when I edit the post there are none.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: creating new cells based on cell value in separate sheet

    Hi & welcome to MrExcel.
    How about
    Code:
    Sub licorice1969()
       Dim Cl As Range
       Dim Ws As Worksheet
       
       Set Ws = Sheets.Add
       With Sheets("Master")
          For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
             Cl.Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(1 * Cl.Offset(, 1).Value)
          Next Cl
       End With
    End Sub
    Change sheet name to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: creating new cells based on cell value in separate sheet

    Yes, that did the trick - thanks!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    468 Post(s)
    Tagged
    47 Thread(s)

    Default Re: creating new cells based on cell value in separate sheet

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •