Posted on 2011-10-18
Last Modified: 2012-05-12

Attached is a file that contains a sample table that I obtain from a Pivot Table displaying the parts of the product that I need to print the stickers.

What I need is that each row displayed in the Original table (Rows 3 to 8) must be repeated as many times as the value in column D ("Qty") of each row.

The Quantity of rows will change according the product to produce it's stickers...
I don't care if the result goes in the same Sheet or in a different one.

Please see the attachment showing the original table and the final result that I need.

Thank you in advance for your help and time,
Question by:Pabilio
    LVL 24

    Accepted Solution

    Roberto - try this, results on sheet2:
    Sub x()
    Dim r As Long
    With Sheet1
        .Range("A3:H3").Copy Sheet2.Range("A1")
        For r = 4 To .Range("A" & Rows.Count).End(xlUp).Row
            .Cells(r, 1).Resize(, 8).Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2).Resize(.Cells(r, 4))
        Next r
    End With
    End Sub

    Open in new window

    LVL 39

    Assisted Solution

    Here's my take on it

    Sub RepeatLines()
    'repeat each line how many times as needed
    Dim lLastRow As Long, lRowLoop As Long
    application.ScreenUpdating = False
    application.Calculation = xlCalculationManual
    application.DisplayAlerts = False
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For lRowLoop = lLastRow To 2 Step -1
        If Cells(lRowLoop, "D") > 1 Then
            Rows(lRowLoop + 1 & ":" & lRowLoop + Cells(lRowLoop, "D") - 1).Insert
            Rows(lRowLoop).Copy Rows(lRowLoop + 1 & ":" & lRowLoop + Cells(lRowLoop, "D") - 1)
        End If
    application.ScreenUpdating = True
    application.Calculation = xlCalculationAutomatic
    application.DisplayAlerts = True
    End Sub

    Open in new window

    LVL 5

    Author Closing Comment

    Dear Thomas and Stephen,

    Both codes works perfectly.
    I really appreciatte your help.
    Thank you very much for your time.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now