Copying data from a closed workbook / replacing indirect(concatenate)

[excelactuary]

Hello,

I need some advice regarding following:

Once a month a workbook is created with reports. The structure of the monthly workbooks looks in 2019 like this:

Statistics Modell 201906.xlsx
Statistics Modell 201905.xlsx
Statistics Modell 201904.xlsx
Statistics Modell 201903.xlsx
Statistics Modell 201902.xlsx
Statistics Modell 201901.xlsx
.
.
.


Every workbook requires data from previous workbooks:


„Statistics Modell 201906.xlsx“ requires data from „Statistics Modell 201905.xlsx“
and from „Statistics Modell 201904.xlsx“
and from „Statistics Modell 201806.xlsx“
and from „Statistics Modell 201805.xlsx“

Basically we require the previous month (2019-05), the month before that (2019-04), the month a year ago (2018-06) and the month before that (2018-05).

The referencing is currently done via INDIRECT(CONCATENATE). To get the references, all five workbooks need to be open. I am looking for ways to simplify the process.

Is it possible to copy the values of the referenced cells via VBA, without having to open/close the workbooks via code? Is it possible to copy data from closed workbooks?

The layout (sheets, data position in the cells) of the workbooks is identical.

Thank you for your help!

This has been cross-posted here: https://www.excelforum.com/excel-pr...placing-indirect-concatenate.html#post5150354