How to copy a cell value from another excel workbook?

Question

Let's say I want to copy Cell C5 from workbook 2 to workbook 1. The name of workbook 2 is a variable given in Cell D1 of workbook 1.

The problem with using Excel's built-in Indirect() function is that the value disappears when workbook 2 is closed. I'm hoping there's a simple VBA macro that can do this.

Thanks in advance.

Answer 1

You seem not to have a lot of experience in Excel VBA, let me give you the general approach for such an exercise:

You can record macros: go to the "Developers" tab, start recording, do the thing you want to do (the copying) and stop the recording. There should be a macro, describing what you have done.

Unfortunately, when doing this, the macro will be something like this:

Source_Range.Copy
Destination_Range.Paste

Generally it is advised to replace this by something like this:

Destination_Range.Value = Source_Range.Value

Good luck and if you have any more problems while doing this, you might ask again by editing your question (I'll be following this question).

Answer 2

The behaviour you're observing isn't a limitation of the INDIRECT() function, but a more general limitation of Excel functions only having access to other workbooks when they're open.

You can use another workbook as a data source by selecting Data > Get Data > From File > From Workbook from the ribbon. Select the source sheet from the Navigator pane, and select Load. This will open the PowerQuery editor.

PowerQuery is an advanced topic which I won't attempt to fully address here, but the default transformation should give you a table containing data from the source worksheet, without relying on that workbook being open in another instance of Excel.