Copying SOME row data into the NEXT AVAILBLE row, in a NEW SHEET


New Member
May 4, 2021
Office Version
  1. 365
  2. 2010
  1. Windows

First time posting, so hopefully I can explain what I need!! Hoping you can help! Sorry if repeat myself I will try and make this as clear as possible!

Would prefer to use formulas, but happy to use VBA if needed, as long as I have the coding, I'm not quite up to speed writing my own codes yet!


I have a list of patients and their details (Sheet 1), who are potentially eligible for surgery.
If they are deemed eligible and are willing to consent. They move into the next stage of their treatment (This is recorded in Sheet 2)
If the patient does chose surgery and consents only some of the patient details will need to be copied into sheet 2. This will be triggered by entering a date of consent for that patient in sheet 1
Not all patients will be eligible or will choose to have surgery, so the list of patients in Sheet 1 will be bigger than in sheet 2, so the data will need to move to the next free row.

Things I don't want to happen.
- the complete row of information being copied into the next sheet
- gaps and empty rows where data has moved into the corresponding row (eg Sheet 1 row 4, to sheet 2 row 4) and not the next available row.

A bit more detail below,

Sheet 1 "Potential Patients"-
Column A, list patients Names,
Columns B - M details their demographics, other patient information and contact details,
Column N, is titled "If Surgery, Date of Consent"

I want to copy SOME of the data from Sheet 1 into Sheet 2 NOT to the whole row. I only want the selected patient details copied into Sheet 2 if Column N is populated (i.e has a date entered)
Data must be entered onto the next available row

For Example - Patient John Smith (who is entered on Sheet 1 Row 11) consented to surgery on 01.05.21, when this date is entered into Sheet 1 N11 the data needed should copy across into Sheet 2 AND into the correct boxes AND
on the next available row AUTOMATICALLY
John is the 3rd patient to consent and so his data will need to populate row 4 (see Below)

Sheet 2 "Consented Patients"-

Column A, Patient Number
Column B, is patient Name
Column C, DoB
Column D, Date of Consent

Column A, Patient Number - is already derived and prepopulated (Nothing to copy across)
Column B, Patient Name - Information from Sheet 1 Cell A11 needs to copy to Sheet 2 Cell B4
Column C, DoB - Info from Sheet 1 Cell E11 will copy into Sheet 2 C4
Column D, Date of Consent - Info from Sheet 1 N11 copies to Sheet 2 D4

Sheet 3 "Patient Follow Up"
There is a lot of follow ups, (20 over 2 years), I have already entered formulas and conditional formatting to indicate when the next follow up is due for these entries, this is workedout from the consent date
However I will need to do the same thing as above to carry across only the relevent data and to organise it into the correct columns.

Thank you so much in advance for your help!

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.


Well-known Member
Jul 3, 2012
Office Version
  1. 2019
  1. Windows
Hi welcome to forum

If not already aware, Excel is not a secure environment to store personal data. Even with locking access to your workbook with passwords, this does not prevent those who know how, unlocking your data. Also, you will probably need to include VBA but before getting too involved, worth checking with employer that it will be OK to do so.

For forum to properly help it is helpful if can share copy of your worksheet(s) either using MrExcel Addin:XL2BB - Excel Range to BBCode

or better still, place copy of workbook with dummy data, on a file sharing site like DropBox & provide a link to it.


Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back