Formulated figures not picking in a macro

Question

"Hello everyone, I'm facing an issue with an Excel VBA macro that involves copying a customer name from one sheet, pasting it into another sheet to trigger formula recalculation, and then creating a PDF file. The problem is that the formulas in the destination sheet are not updating when the customer name is pasted via the macro, even though they update correctly when I manually paste the name. This leads to incorrect values in the PDF file that is generated. I have tried different approaches, including delays and calculation methods, but the issue persists. Has anyone encountered a similar problem or have any suggestions on how to ensure that the formulas update correctly before creating the PDF? Your insights and suggestions would be highly appreciated. Thank you!"

Answer 1

Hi,

Here are a few suggestions-

Check Calculation Settings: Verify that the calculation settings in Excel are set to automatic. You can go to the Formulas tab, click on Calculation Options, and ensure that "Automatic" is selected. This ensures that the formulas recalculate automatically.

1. Force Calculation: Before generating the PDF file, you can explicitly trigger a calculation in the destination sheet using the Calculate method.
2. Wait for Calculation: If the formulas take some time to recalculate, you can introduce a brief pause in your VBA code using the Application.Wait method.
3. Ensure Events are Enabled: In some cases, events may be disabled, preventing the automatic recalculation of formulas. You can check if events are enabled by going to the VBA editor and ensuring that Application.EnableEvents is set to True.

I hope this works!

But there is also a possibility that your Excel workbook got corrupted. For that, you can try to repair Excel file using the Open and Repair method.

Best Regards.