Moving Row Of Info To First Empty Row On Another Sheet Into Specific Columns?

[URL]

I have a workbook which serves as a master database at work. it contains two sheets: current residents of our facility (let's call this Sheet1), and those that have left/been discharged (Sheet2). It contains 87 rows and 34 columns of info.

I'm looking for a macro that will do the following:

When I click on a cell in any row (i.e. I need relative references) on Sheet1, the macro will (once activated):

1. Select the info between column D and column AH (inclusive) on the row where the selected cell is... i.e if I click on cell F4 before the macro is started, it will select the info from D4:AH4.

2. It will COPY this info

3. It will paste the info into the first empty row on Sheet2, starting from column C (i.e if the first empty row is 200, it will paste the info from C200:AG200)

4. It will then have a popup asking for: a. 'Date Left' and b. 'Reason', with two empty fields to input the info into. 'Date Left' is (obviously) a date value and 'Reason' is a text string. Once OK is hit on this popup, the Date Left will be pasted in column A of the same row (in our example, A200), and 'Reason' will be pasted into column B (again, in our example B200).

5. The macro will then go back to Sheet1 and delete the info that was selected between Columns D and AH inclusive (in our example, D4:AH4)

6. The macro will then save the workbook.