So I am decently new to VBA scripting and am trying to do a few automations to a worksheet. I have been working on this for several weeks and would like to have an experienced coder take a look and help me clean up the code and possibly use some more variables so that I can cut out some lines of code repeated for each case statement.
I also am getting an error on part of my code to fill another worksheet from a user form. Let me below explain what I am trying to do.
I will have 8 worksheets (“Pipeline”, “1-forecast”, “2-forecast”,”3-forecast”,”4-forecast”,”5-forecast”,”admin”,”report”) (The names will change once the excel file is working and I can go into the code and rename the tabs in the vba coding.
The first sheet will have a button that invokes Userform1 (a form with a bunch of textboxes and a few comboboxes (the comboboxes will pull their options from named ranges in the admin sheet). Users will use the form to input their data. Upon hitting submit, it will input all the fields into the next empty row of “Pipeline” as well as into its respective sheet (if the user specifies it is for “1-forecast” or 2,3,4,5-forecast it will populate the next free line on that sheet as well) I have code that assigns a uniqueID to each entry and it will be added into a hidden column, that way updates and editing on one worksheet can update the same value on the other worksheet by searching for the same UniqueID.
Now, sheets 1-5 forecast will have 3 or 4 different groupings (“current projects”,”projects won”,”projects won/commit”,”Strong Upside”) the userform will have a dropdown where they will choose the status. so on submitting, not only will the row be populated in “pipeline”, and on its respective sheet, but it will need to be in the next free row of whatever its status is as well, on that sheet.
The last thing that I need with this is to be able to select different cells (for example lets say that I already have several entries in my workbook. I need to be able to change the status value on an entry and as the status changes lets say from “Strong Upside” to “Projects Won”, the entire row will move to the next empty row under “Projects Won” on that sheet and the status will update on “Pipeline” . Or be able to delete an entry and have it be deleted from “Pipeline” as well (this is easy if we use the unique ID).

Hope this makes sense. Like I’ve said, I’ve done quite a bit of it already but I’m sure its sloppy and could use a mentor/expert to help me tweak it or re-write parts of it to make it work. Thanks!

Not sure why it won't let me upload my sample excel workbook, so anyone interested in taking this project on, if you will message me I can email my scrubbed example.