copy values from a worksheet to another workbook. source workbook name unknown

I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.

Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.

I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.

How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?

Hello sark666,

Here is an example macro. This will let the user choose where the Excel source file is stored and open it. The macro will also ask the user to open the destination workbook. The information will be copied from the source range to the destination range. Change the sheet name and ranges to what you are using. They are marked in red.

Please Login or Register  to view this content.

Hello sark666,

How to mark a thread Solved
Go to the first post
Click Edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No Prefix.
Change to Solved
Click Save