Move certain data from one column to another sheet based on selection in drop down selection

  • Hello all,

    I was introduced to this community as I was told it's the place to go for help on VBA.

    I've got a project where after someone inputs data in columns A-H in sheet "Quotes" (Picture 1), the user would then select whether to open or close a job in Column I using a drop down I've already made. Upon selection, I'm trying to have the column move to either the "Closed Quotes" or "Open Jobs" based on the selection. When it pastes into "Closed Quotes", I'd like it to also mark Column J with the date that the selection was made (in the "Closed Quotes" sheet). For "Open Jobs", I'd the data to do the same with the date but also but delete the data from rows A-D and only paste rows E-I starting in Row C (so Row C to G [Picture 2]) rather than start in Row A, where I've struggled.

    I know this is a lot but any help would be greatly appreciated.

    Thank you so much

  • Hello Alexiz,

    Could you please upload a sample of your file as images are not much help to anyone who may offer you assistance. We can easily test procedures with an actual file. Ensure that you use dummy data in your sample just as your images appear to have.



  • You are making work for yourself and resorting to VBA unnecessarily.

    Your data should be stored in one sheet with a column for Status. Then you wouldn't need to move data. You simply filter to look at the Status, you can even simplify this by using Slicers.

    Data in Excel

    Also consider converting the data to a Table then you wouldn't need all those unused formulas which is bad practice.

    Overview of Excel Tables.

    Spend some time learning to use Excel correctly before thinking about VBA, especially when you are resorting to Loops which are generally an inefficient way of doing this type of thing.

  • Hello Alexiz,

    Should you want to stay with your current method, a worksheet_change event code should do the task for you:-

    With this code, each time you make an "Open" or "Close" selection from the drop downs, the data will be immediately transferred to its relevant sheet.

    To implement this code:-

    - Right click on the "Quotes" sheet tab.

    - Select "View Code" from the menu that appears.

    - In the big white code field that then appears, paste the above code.

    You'll need to first delete all existing codes that you have.

    I've attached your sample with the code implemented and all other codes removed. Test it to see if it's what you were hoping to achieve.

    I hope that this helps.




  • Thank you all very much for your help with this. I am able to now build myself a simpler than initially planned excel document to help with my organization.:thumbup:



Participate now!

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