Copy from horizontal and Paste into vertical VBA Code

I have some data coming from Access database based on the Scenario ID. After being inputted in Access database the results will paste in Sheet1. I need the information in Sheet 1 to be extrapolated to Sheet 2 from horizontal to vertical. Once value one from Sheet 1 , goes to Sheet 2 and fills out vertically, I need it to go check if value 2 is present in sheet 1 and do the same thing, i.e bring it to sheet 2 vertically. If value 2 is not there then it needs to stop at that point.

In my example case I have 3 scenarios, so after copying and pasting 3 scenario from sheet1 to sheet2 it should stop however, if there was a value 4 in there it needs to continue until there are no more values to be extrapolated to sheet 2. Attached is a sample copy for reference.

Any Help Will be appreciated..