Novice to VBA and wanting to verify that I can complete these steps to create a Macro

Joined
Oct 19, 2022
Messages
2
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. MacOS
I am trying to improve some of the processes we have at work to minimize emails and eliminate redundant tasks. I am generally pretty good with the formulas for Excel, but I have not used VBA/Macros at all. What I'm wondering is if it is possible to do the following below. I want to learn how to do this, and I'll be posting as I go to ensure I'm doing it correctly, however, before I get started, I just want to make sure I'm not shooting for the moon with what I'm hoping to do. Essentially, I want the macro to run automatically and I need two macros to run simultaneously.

I have two worksheets labelled:
1. Active Corrections
2. Pending Corrections

Here is the Active Corrections Sheet:

Screenshot 2022-10-19 092631.png


What I'd like to be able to do is create the following:

An automatically running Macro that does three things:
1. Any rows with an X in Column I and a date in Column H will delete within 5 days of the date in Column H.
2. When Columns A-G are filled, Pending automatically populates in Column I.
3. Any rows with Pending in Column I will be copied to Sheet2, Pending Corrections.

The idea is, our administrator will copy and paste information in columns A-G on a weekly basis to this workbook, which will be viewable by all staff. When the email is sent out by our admin to correct any documentation in patient records, staff will come to the spreadsheet and update it once they have completed their corrections. When they put an X in the box and the date of correction, this will remove it from sheet 2, so that the administrator will only see the records that still need corrected (this eliminates being emailed multiple times for something one has already corrected). The X and date of correction will also trigger an auto delete after a week so that the spreadsheet does not continue to grow, but is paired down each week prior to adding more corrections.

Hopefully that gives a good idea of what I'm trying to do.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You can do virtually anything that you can come up with. None of that is overly difficult IMO.

Would you not want to send another email if not resolved within x days from the date of that email? You'd need an 'emailed' flag to prevent unwarranted emails, I think, but you seem to be relying on them to resolve the issue in a timely manner without follow up emails where they have not. Perhaps that would be manual effort. Having an x and a date is redundant. If there's no date, it's not done.

"Automatic" macro depends on your definition of automatic. Nothing will run unless a workbook is open and someone has to do at least that much.
 
Upvote 0
You can do virtually anything that you can come up with. None of that is overly difficult IMO.

Would you not want to send another email if not resolved within x days from the date of that email? You'd need an 'emailed' flag to prevent unwarranted emails, I think, but you seem to be relying on them to resolve the issue in a timely manner without follow up emails where they have not. Perhaps that would be manual effort. Having an x and a date is redundant. If there's no date, it's not done.

"Automatic" macro depends on your definition of automatic. Nothing will run unless a workbook is open and someone has to do at least that much.
The follow up email would be amazing, but I wasn't sure if it would be possible to set that up. We do have a column for each person that is responsible for the correction, so I assume based on what you're saying, we can tag an email address to each of those individuals and if they haven't corrected it by a certain date, they will get a follow-up email?

Solid point on the X. I guess I was thinking that I needed a column to show pending, but I guess I could do a conditional format so that if the date cell is blank, then the row is still red and thus, pending without stating it.

I'm assuming the workbook will always be open on someone's desktop, as several hundred people will have access to this workbook. However, even if it's opened after no one using it, I just want the macros to run automatically so there is no guess work or buttons or any reason for pushback from the admin staff. Essentially, I'm trying to make it as user friendly as possible. And perhaps, just trying to avoid getting an extra 30-40 emails every few days with people replying all, only to end up still being flagged for something I corrected a week ago. haha
 
Upvote 0
The follow up email would be amazing, but I wasn't sure if it would be possible to set that up
That's a matter of IF email sent has a date but date corrected has no date AND x days have elapsed, send another email. This time the message body says 'this is a reminder' or something. Yes, multiple people could be emailed per MRN or whatever the case is.
Yes, CF can highlight a row if date is missing.
Code can be made to run upon wb opening as well as every x time interval. As for sharing, I'm not real savvy on how Excel shares workbooks. My forte is Access databases and I do understand sharing in that case.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top