Two questions about adding rows, copying data between multiple Excel files

Dear Excel specialist,

A have question, but first allow me to provide some context.

I created VBA code that opens sequentially all workbooks in a source directory (all files contain the same format/structure, but the contents vary).

At high level:
- Data is sequentially copied and paste from the (source) workbook files, range (R7:S500) to 1 destination file
- File is closed, next file opened
- All data (again) will be past in the destination file, but one column to the right -> (next column i=i+2 counter), otherwise the original data will be overwritten.
- Redo from start until no files are found

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Currently, I have 2 challenges.

1. I don't want to open the (source) workbooks (visually) while gathering the data.
I would like to have a kind of background process. For example: (https://www.exceldemy.com/excel-vba-...thout-opening/)

2. Each file in de source directory contains this cell information (content may vary)
a. Refresh_Date = Cell B1
b. Department_Name = Cell B2
c. Domain_Owner_Name = Cell C1

I would like to add these (a, b, c) at the top of each (to be added) column that is filled with the data of the source files.
I am trying to achieve this, but unfortunately, it does not work.

-----------------------------------------------------------------------------------------------

For example (please see attached example)

Blank row
02-02-2023 (This is the date field from cell B1)
Finance (This is the department name from Cell B2)
Piet Compete (This is the owner name from Cell C1)
Blank row

(R7:S500) data from the range.


-----------------------------------------------------------------------------------------------

Is it possible to help me out with this question (btw, I am not a programmer)?
Normally I add the original Excel files to provide an example, but this is not allowed, because it contains a lot of company information (i.e. confidential).

Thank you in advance for any support.

Please Login or Register  to view this content.

Hi k1dr0ck,

Your code works like a charm. Thank you for that (very happy)!
I have just one small question...
Some cells contain references; therefore, some results are showing a #REF error in the "paste" result.

Is it possible to copy the cells without the references. So only the values with the existing markup like colors, borders and so on?
Please see attached file with the result.

Thank you very much!! 😊

This does not work...

Please Login or Register  to view this content.

Hi k1dr0ck,

Thank you very much for you help. I am very happy with the results (works like a charm!)
I will close the thread and add reputation.
Thank you again.

It works!! :-)
Thank you again!

since you were actually using formulas your initial code was actually better than using an array

Please Login or Register  to view this content.