Reference a WB with filename based on cell value in current WB(subscript out of range)

[cloudberr]

Hi,

I've been trying to get this to work for ages now and I can't get my head arount it but I feel like it should be doable.

I want to open another workbook from which I get data, paste it into the current workbook, then modify and paste into yet another one.

However these other workbooks have filenames that may change and I want to input the filename that interests me e.g. in cell C4.

I would love to cut down on the code and not have to use Workbooks.Activate each time...


Current code excerpt:

Please Login or Register  to view this content.

The code that fails:

Please Login or Register  to view this content.

It gives the "subscript out of range" error each time on the "Set wb" row.


I wonder if this is possible and if so, what am I doing wrong...

Cheers and all the most Excel-lent in 2016
Cloudberr

[Greg M]

Hi there,

If the workbook is already open try:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[TMS]

If the intention is to open a workbook, then you need to use Workbooks.Open

Please Login or Register  to view this content.

Regards, TMS

[cloudberr]

Hiya,

Both of these work!

It's funny but I'm certain I've tried something with Workbooks.Open and especially something like in Greg's version, but still couldn't get there.

I've tested these with three workbooks open to see if copy-pasting works correctly between them and it does as it's supposed to. I'm super happy now.

I'll probably be using the TMS's code since it's a bit more concise - it makes sense to open it and reference it in one go I think.

Big thanks guys :-)

[TMS]

You're welcome. Thanks for the rep.

[Greg M]

Hi again,

Many thanks for the Reputation increase - much appreciated

Best regards,

Greg M