Macro to add # of rows based on data in the sheet

Closed
Report
Posts
2
Registration date
Friday May 24, 2013
Status
Member
Last seen
June 11, 2013
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
I am trying to make a guest list that will insert rows automatically and fill in some data.

I can get the primary data from one worksheet (i.e. company name & # of tickets) then I'd like to have the worksheet automatically insert the # of rows equal to the # of tickets purchased (less 1). I'd also like the company name to populate in each of those rows. Is there a macro that can do this?

Example: Company A has 25 tickets (Company is in column A, tickets is in column B). I want the worksheet to insert 24 rows with "Company A" in column A.

Thanks

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
could you post a sample book at some public file share site and post back link to the posted file back here and re explain what you seek ?
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Ardale,

Here you go:
Sub test()
Dim x, y, cRow As Integer

cRow = 1

Do
    If Range("B" & cRow).Value > 1 Then
        x = Range("B" & cRow).Value
        y = Range("B" & cRow).Value
        Do
            Range("A" & cRow + 1).EntireRow.Insert Shift:=xlUp
            Range("A" & cRow).Copy Range("A" & cRow + 1)
            x = x - 1
        Loop Until x = 1
        cRow = cRow + y
    End If
Loop Until Range("A" & cRow).Value = vbNullString

End Sub

Best regards,
Trowa
Posts
2
Registration date
Friday May 24, 2013
Status
Member
Last seen
June 11, 2013

Thank you so much. I tried it and I get an error when it runs. Is there somewhere that I can send you a sample copy of the file so that you can see what the error is?
Thanks,
A
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Sure, you can google "filesharing site" or give these two a try:
www.speedyshare.com
ge.tt

Post back the download link provided by the site of your choise.
Also provide the code line that produces the error.

Best regards,
Trowa