Excel file won't save after running macro

You can attach a file to your post. Make sure to clean out any information you shouldn't share.

Re: "The macro runs perfectly fine (takes around 20 minutes to complete)"
That's a matter of opinion of course.
Had a quick peek at your attached text file, which does do no good at all, and I am sure someone can come up with a much better solution.
For one thing, selecting is frowned upon because it is mostly not required and slows everything down (hence the 20 min).
However, kudos for you doing what you did.
If you attach a thumbed down version of your excel workbook without personal data, someone will have a solution that finishes, if it is slow, in 20 seconds instead of 20 minutes.
Make sure the workbook is setup like your original. Exp[lain what you want and it would be best if you have a before and after example.

Originally Posted by jolivanes

Re: "The macro runs perfectly fine (takes around 20 minutes to complete)"
That's a matter of opinion of course.
Had a quick peek at your attached text file, which does do no good at all, and I am sure someone can come up with a much better solution.
For one thing, selecting is frowned upon because it is mostly not required and slows everything down (hence the 20 min).
However, kudos for you doing what you did.
If you attach a thumbed down version of your excel workbook without personal data, someone will have a solution that finishes, if it is slow, in 20 seconds instead of 20 minutes.
Make sure the workbook is setup like your original. Exp[lain what you want and it would be best if you have a before and after example.

Haha well when I say fine I mean it gets the job done, clearly the script is very inefficient.

I just uploaded a before and after version of the workbook file pared down to only two physicians. The goal of the script is to copy over the relevant columns of data from the Procedures_D and Procedures_E sheets into a variety of other sheets in order for it to calculate the total number of each type of procedure completed. There are formulas in the other sheets that calculate these totals. Finally those values are copied over into the full progress sheet to be displayed.

Is there any other information you think I should provide to help others understand the issue at hand?

You are correct, the L is a typo, nice catch!

The total number of columns for my Procedures sheets is 669. I just set it to go out to 700 in order to cover any additional columns of data being added in the future.

Please Login or Register  to view this content.

If the value in cell V4 is 0 it sets the value of the cell to 0, if the value in cell V4 is any number greater than 0 it sets the value to 1. I'm not sure why it looks so funky but it seems to work XP

After you autofiltered "Procedures_D" sheet and run this, does it transfer the right data?
This is only the part to transfer data. (Above this line: PURPOSE: Find & Replace a list of text/values throughout entire workbook)

Please Login or Register  to view this content.

That funky formula could be changed to =If(V4>0,1,0) I think.
The quotation marks around the 1 makes it text. Is that what you want?

To avoid possible mistakes when you do it manually, I just did the following.

Add a sheet to the end in workbook "Full LL Test_Before.xlsm"
Name it "Temp_Sheet"
Enter all the names (Column Headers) you need in Cells C1 and down in this Sheet.
Now run the following code.

Please Login or Register  to view this content.

Now you'll have the Column numbers for "Workflow_adherence" in Column B and for "Procedures_D" in Column A

You can use a similar approach for the other code.

Do yourself a favo(u)r and Google something like "Why avoid using select in Excel vba"

It looks like to me that lines 203 to 509 could possibly be done with SUMIF Function without filtering.
I think that you should tackle one problem at the time and Post it in this Forum with a clear and concise explanation what you want to achieve.
Once one problem is solved, go to the next problem that is dependent on the previous solved one.

Sorry for the late reply, I've been traveling today.

So correct me if I'm wrong but that script is checking to ensure that the correct columns of data are being copied over from the Procedures_D sheet?

Thank you for this suggestion, you've already been more helpful than anything I expected. I'll be sure to take further advantage of this forum, it's an incredible resource.

Yes it is.

I did as you suggested and added the Temp_Sheet. Since I added your script to Sub E as well, I added column checks for the P1_workflow sheet too. I just ran it and everything is running beautifully.

It could be more efficient of course as you identified between lines 203 and 509 but it's running much faster than before and now it's not overwhelming the systems memory so I'm able to save and exit without excel crashing.

I'll make another forum post to address the remaining inefficiencies in the macro but you have helped me to solve my main issue. Once more a heartfelt thank you