Script removes formulas in the source file.

Dear Excel-specialists,

I have a question.
I have a script that opens all source files in a directory (Excel sheet with the same format) and copies the content from a specific range to a destination Excel sheet.
This script works fine (created with the great support of a member of this forum), but as mentioned in the title, this script also removes the formulas in the source files.

This even happens with the command --> ActiveWorkbook.Close SaveChanges:=False or --> ActiveWorkbook.Close False
I checked where the change in the formulas happen, and I think that this is the suspect (.Range("R7:S500").Value = arr ), but I am not quite sure.
Can anybody help me with this question? Unfortunately, I cannot share the Excel files because this is company confidential information (sorry for that/normally I add examples).

Please Login or Register  to view this content.

Hi CheeseSandwich,

Thank you for your help.
I don't get an error, but now I get an #Verw error in the source file (Dutch message).
The original file contains formulas, and I would like to copy the value of the formula to the destination file without altering the source file.

For example
Original file
=AANTAL.ALS(F$7:F$500;R25) (output value from the formula 123)

Destination file
123

Please see the attached result.

I just did that and:
- Strange..? This code does not add value (from what I can see).
- The data is copied, but I keep the #Ref error in the destination file.

What I would Like to achieve is (as a kind of use case:

1. Open all Excel-source in a directory. Most preferably at the background (Read Data from a closed Excel file without Opening it)

2. Copy specific cells with values so they are positioned at top of each column (this code works).
Refresh_Date = .Range("B1").Value ' Read original source cell data (input varies per opened workbook)
Department_Name = .Range("B2").Value
Domain_Owner_Name = .Range("C1").Value


3. Copy all values from the formulas that exist in the source files (range R7:S500) to the destination file, below the Refresh_Date, Department_Name and Domain_Owner_Name

4. Add a new column that collects the new data from the next source file, until no files are left.

With attachment

Thank you very much for helping me.
The formulas stay intact/ok now, but I lost my markup (lines, colors, borders and so on).
Is this easy to fix?

It works like a charm!!
Thank you very much for your fast support and effort!!

Adding reputation
Thank you (again) for you help.

Originally Posted by CheeseSandwich

You're welcome, thanks for the rep.

No problem (deserved)! :-)