Reformat an Excel Database-CodePudding

I'm a new user to this site but come here regularly to find solutions to my Excel questions. So first off, thank you for the information the people that answer questions give to me. I signed up to hopefully get the same help for a specific challenge I am facing.

In the excel table I attached, the table on the top shows the current format I have a database in and I am trying to write a script to change it to the format on the bottom.

I have code (that I found on this website) that takes the value in column E and inserts rows under that amount. However, I am having difficulty figuring out how to copy the original columns A:E to the newly inserted rows and how to reconfigure the database values in F through L to the new rows.

Is there anyone that can help me with this? I would greatly appreciate it!

Thank you.

Chris

This is the code that the forums helped me find to insert rows based on a column E. It works, but now I am stuck.

Sub Insert_SB()
Dim lngCounter As Long

For lngCounter = Range("E" & Rows.count).End(xlUp).row To 2 Step -1


    With Cells(lngCounter, "E")

    If IsNumeric(.Value) And .Value > 1 Then
    With .Offset(1, 0).Resize(.Value - 1, 1)
    .EntireRow.Insert
    End With
    If IsNumeric(.Value) And .Value = 0 Then Exit For

End If
End With

Next lngCounter

End Sub

CodePudding user response：

You can achieve this without VBA by using Excel Power Query (Menu Data, "Get and Transform"). In Power Query you only have to select the columns that you want to unpivot and choose Unpivot columns. That will do the job.