Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to automatically move row

0

Automatically move a row from Compliance_Conditions to Compliance_Received when a date is entered in the Date Received column of the Compliance_Conditions sheet. Also delete the row from the Compliance_Conditions sheet after it is moved. The sheets that are moved to Compliance_Received need to be sorted with the most recent date at the top. Can anybody help please. Kind Regards

Answer
Discuss

Discussion

You are asking for fairly complex coding which you are unlikely to be able to modify yourself when (it's always "when", not "if") changes are made to the system. Simpler and more flexible (meaning it could adapt to most changes itself) code would have the added advantage not to go against Rule #1 of good data storage: "Never modify the data first entered."
I recommend you only maintain the "Conditions" sheet and create the "Received" sheet on the fly, at the click of a button, whenever needed. Another button (macro) gives you a list of unreceived "Conditions" and a third one reverts the data to the original comprehensive list. Most likely, you don't really need any of the two sub-lists - you probably only need the view of them, perhaps printable. However, if you do need snapshots of what the status was, say, at the beginning of each month, the macros could make copies of the filtered data, too. Let me know if you would consider such a solution superior to what you first had in mind.
Variatus (rep: 4889) Jun 9, '19 at 3:09 am
Add to Discussion

Answers

0
Selected Answer

I programmed your worksheet along the lines I described above and attach the file. The Scroll-Into-View function doesn't work quite as it is supposed to when switching from one filtered view to another but you can see that the programming is minmal with the greatest effort placed on user control.

Right-click on A1 and select one of the 3 choices offered by the first menu item. The menu item is available only from A1. Some of the code was lifted from Ron de Bruin and then modified for your project. He comments his code very nicely and I tried to keep up the good practise.

Let me know if you like the project and what further modifications you might require.

Discuss

Discussion

Thanks or taking the time to answer. Great idea with the 3 views of Pending, Received and All. Also, thanks for the tip on data storage. 
TreacyMac (rep: 2) Jun 9, '19 at 3:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login