VBA to move row to second sheet based on cell value keeping formatting

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi,

    I have a working code (for the most part).

    I have 2 issues with this code which I can't figure out how to get past.

    Purpose of VBA

    Workbook contains 2 sheets, the first is named Prospects the second is named Closed

    When the command button is clicked, the entire Prospects Sheet should be scanned for the value of "Closed" in column P.

    Any row that has "Closed" in column P should be moved into the first available empty row(s) in the Closed Sheet

    Any row that is removed from the Prospect Sheet needs to be deleted and shifted up so that there are no empty rows between data and all formatting remains

    Issue 1

    When the "Closed" row is moved to the Closed Sheet I lose the formatting in that row on the Prospects Sheet. I have Data Validation set in certain columns on the Prospects Sheet and that's gone once the VBA runs.

    Issue 2

    The code seems to only work for one row at a time. I'd like the code to grab all of the rows that are marked as "Closed" in column P on the Prospects Sheet, following the same criteria as explained above.

    Thank you in advance,


  • That's completely the wrong way to work with data. All data should remain in one sheet then filter for Closed. There's no need to have two sheets

  • If you still think it's necessary to move the data then assuming your data is in a table format starting in A1 this should be much faster than your loop.

  • Hi Roy,

    Thank you for the prompt reply. I understand that filtering is the simplest way to do this but I'd really like to be able to keep open prospects on a separate sheet from closed items if possible. I really don't want to have to keep manipulating the sheet to separate those values.

  • Hi Roy,

    Thanks I posted that before I saw your code. I tried it and what it seems to be doing is Auto Filtering the Prospects Sheet, hiding anything that isn't closed. If I have to use Filtering then as you said there's no need for a second sheet or VBA.

    There's a couple of things I want to clarify.

    1. Rows 1 through 9 are hidden. They contain Data Validation lists.

    2. Header values are in A10 and across. All data that needs to be assessed reside in rows 11+ starting in column A.

    3. It is a basic Excel sheet, not a table.

Participate now!

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