Macro fails to write after modifying it to force Excel to update screen twice during run

Macro fails to create data after changes were made to force Excel to refresh screen twice (at the routine beginning and prior to the ending Message Box)  as signals and feedback to the user.

File with macros is attached with more details of changes made that possibly caused this problem  SummaryOfWorkbooksRev13.xlsm
Berry MetzgerLean process improvement consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Did you add this line of code to clear the cells?

If slr > 3 Then Sws.Range("B4:F" & slr).ClearContents

Open in new window


How can we run your code to see what is being extracted in col. B:F from your target workbooks?

Can you upload one of your target workbook which you think will meet the criteria for extracting the data into the macro workbook?

Also run your codes separately (the codes which produced the desired output before making any changes in the codes) on this single target workbook which you are supposed to upload here and copy the values after the code execution to another sheet to know what should be the correct output when we run the problematic code and compare the values after running the problematic code.
Name the sheet with the desired output as "Expected Output"

Also mention all the lines here which you have added in your codes and also mention the sub-routine name where these lines have been added.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I can't test this but when I compiled your code via Debug->Compile VBAProject it highlighted the "Left" method in the If Left(fso.GetExtensionName(fil), 2) = "xl" And fil.Name <> Dwb.Name And Left(fil.Name, 1) <> "~" Then and gave me a "Can't find project or library" error, and when I clicked 'OK' it listed two missing References.
Missing ReferencesI unchecked the two missing references and recompiled and it was OK. Try it and see if that fixes the problem.
0
Berry MetzgerLean process improvement consultantAuthor Commented:
Thanks to both of you.  This question was a false alarm!
Your questions led me to explore more deeply into why the macro was not writing data.  The source of the problem was that I ran the improved macro against old versions of the 10 workbooks I used to develop the macros initially, not newer workbooks with values in column H to copy.  It was me not the macro that was cause of the error.
Many thanks sktneer for your persistence in getting this macro to operate on multiple workbooks.
Berry
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Berry! Glad you were able to resolve the issue successfully. That will give you a kind of confidence to debug your future codes on your own. :)
0
Berry MetzgerLean process improvement consultantAuthor Commented:
This macro is growing legs! Since it is saving 1.5 hours per day at a cost of 45 seconds, more manual processing of a labor-intensive task requires more development effort.
I will open a new thread to request further functionality using similar multi-workbook looping.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.