Code to copy and insert rows

[heylookltsme]

I'd love some help with this bit of code I'm trying to write. I'm completely new to VBA, so I know what I want to do conceptually, but I really don't know the syntax.

I'm using an Excel spreadsheet to create a sign in sheet for events. Each row contains info about each organization attending the event, including a column with the number of tickets they purchased for that event.

What I need to do with my VBA code is create a row for each ticket. So, for example, if Organization X has 3 tickets to this event, there should be three rows with info on Organization X (i.e., copy that row and insert it twice).

Here's the pseudo code I've written out:

Declare array to store all values of the current row (rowValues)
Declare variable to contain current row number (currentRow)
Do While row(currentRow) is not empty
Store values of row(currentRow) in rowValues array (for loop maybe?)
If rowValues[value for number of tickets] > 1
For i=1 to rowValues[value for number of tickets]
Insert new row
Loop through values in rowValues and paste them into the new row
currentRow += rowValues[value for number of tickets] // So that the currentRow moves down below the rows we just created


One thing to note: there are 11 columns in the spreadsheet, so the rowValues array would be set to hold 11 values. (Do arrays in VB start at index 0 or 1?)

So there it is conceptually. Any help with the syntax would be greatly, greatly appreciated! Thanks!

[Bob Phillips]

Sub addRows()
Const TicketCol As Long = 2 '<<<< change to suit
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, TicketCol).Value > 1 Then

.Rows(i + 1).Resize(.Cells(i, TicketCol).Value - 1).Insert
.Rows(i).Copy .Cells(i + 1, "A").Resize(.Cells(i, TicketCol).Value - 1)
End If
Next i
End With
End Sub

[heylookltsme]

Works perfectly! Thank you so much!!

[mdmackillop]

Welcolne to VBAX
If this is Solved, please mark it so using the Thread Tools dropdown