How to copy values from one OPEN workbook (with variable file name) to another

Hello there :-)

Every month I receive a batch of new files with names such as Analysis_Report_0001, 0002, 0003 etc. The Analysis_Report string is always there but the numbers are changing - you get the idea.

Then I have a file Analysis Summary where I need to input certain values from the Analysis_Report files.

Here's the code that works (I have both files already open and the macro is in the Analysis Summary file).

Please Login or Register  to view this content.

What I need to do is to have a macro nested in the Analysis Summary file, that would activate any OPEN workbook that contains the "Analysis_Report" string in its file name, copy the selected range, and paste values to the first empty cell in the column C.

Now, I don't need the macro to open the Analysis Report file, as I already have it open, and I need to do without references to C: stuff - the path would change every month anyway.

I've spent the last two days comming out with a solution, and I'm going gray by now :-)

Any help will be much appreciated.

Please wrap the code in your post with the CODE tags. Select your code and click on the # button.

Try this

Please Login or Register  to view this content.

Try this

Please Login or Register  to view this content.

@Kitko,

Please test with the code from post #5, there were some typing errors which I have corrected.

Maybe this

Please Login or Register  to view this content.