Copy, Paste data and then delete source data

  • Hi,

    I have a spreadsheet that has two worksheets "HERS Data" and "Archived Data".

    HERS Data is the live data sheet containing training records of employee, there are multiple rows per employee due to number of assessments and certificates.

    Column A contains an Op ID Number the same number is assigned to every row data for that employee
    Column B is the employee name
    Column K is contains a unique Record ID

    I have a userform with TextBox1 employee Name and a command button cmdArchive

    What I want to do is when I enter the employee's name in TextBox1 and click cmdArchive all rows of data relating to that employee are copied from HERS Data to Archived Data and then the source data in HERS Data is then deleted.

    Can anyone help please been trying for days to find away to do this.


  • If you use Excel tables (called ListObjects in VBA) for the two ranges, it's pretty straightforward.

  • For this to work, you'll need to turn your two lists into tables if they are not already. You can use the Format as Table button on the Home ribbon to do that (just select any cell in the list and then click that button).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!