I have rows of data that relate to hotel reservations - one row per reservation. I would like to split each reservation into one row per night.
I need a macro which will do the following:
- Look at value in 'nights' column "n"
- Insert "n-1" rows beneath row
- Copy all values into each new row except for one column 'Stay Date'
- In the first new row 'Stay Date' = check in date + 1
- In the second new row 'Stay Date' = check in date + 2 etc.
So far I have the below code which creates the new rows, but I am stuck on populating them.
- Sub testProc()
- Worksheets("Source Data").Activate
- Dim r, count As Range
- Dim LastRow As Long
- Dim temp As Integer
- Set r = Range("A:CA")
- Set count = Range("P:P")
- LastRow = Range("P" & Rows.count).End(xlUp).Row
- For n = LastRow To 1 Step -1
- temp = Range("P" & n)
- If (temp > 1) Then
- Rows(n + 1 & ":" & n + temp - 1).Insert Shift:=xlDown
- End If
- Next n
- End Sub
Any help would be greatly appreciated!