Copy sheet from closed wkbk to any open book, using VBA that's in personal.xls

Hello,

For some reason this is just kicking my fanny, and sure would welcome some wisdom from the group.

I have a sheet, called "Durations" in a closed workbook (CertDurations.xls) that resides in a common location on a network drive (S:\ABC\LongTerm\Group One\XYZ). It contains about 300 lines of certification titles and corresponding durations (in years) that each is valid. From time to time the durations change, so I want to leave it in a common location so the code accesses up to date information when it does a vlookup against it. I have another workbook, the output of a data export, that will be open and can be of any name. It will likely be on the user's desktop, but not necessarily. What I can say is that it will be open. The VBA code itself is embedded in each user's personal.xls and linked to a custom button on the toolbar.

What I want to do is very reliably copy the sheet "Durations" from that workbook "CertDurations.xls" into the open workbook, no matter what the latter's title or location, and then use that data in my vlookup routine. I know there's ways to do this w/o opening "CertDurations.xls", but if it's more reliable, it's ok to open it, I'd just also need to know how to close it.

Sure could use some help on this one. Thanks much.
Frank

Sorry, was not precise in my choice of words. What I need to do is copy the values on the sheet "Durations" to the open workbook, regardless of what that opened workbook is named....when the code driving it all is in that particular user's personal.xls.

Thanks,
Frank

Thanks everyone. I found a workaround ... created a dialog prompting user to do set of things that I know work.