Excel-Importing data from one workbook to another?

I have a report I am working on for my company (AIS_Cert_43.xls). Within this report, I have a tab that list all of our equipment on it along with info for each piece of equipment. Our machines are calibrated at different dates through out the year so we need a way to keep track of these.

I want to have a second workbook (AIS Certmaker Gauge List.xls) that contains only the machine information and calibration dates so it can be updated on a regular basis. I have a button in my first workbook that I want to be able to click and have it open the gage list workbook, copy the machine information, paste it into my report workbook, then close the gage list workbook.

Here is the code I have so far, the part in blue is what keeps hanging me so far:

Code:

Private Sub CommandButton1_Click() ' ' AIS Gage List Update Macro ' Macro recorded 3/5/2008 by AIS / Mike Hemm ' ' Dim Msg, Style, Title, Response, MyString Msg = "Update may change selected equipment. Are you sure you want to update the list ? If yes, select no when asked to save changes." ' Define message. Style = vbYesNo ' Define buttons. Title = "Gage List Update" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then ' User chose Yes. Workbooks.Open Filename:= _ "F:\certification\AIS Certmaker Gauge List.xls" ' Change this location whenever the gage list changes locations ActiveSheet.Range("B5:F300").Select Selection.Copy Windows("AIS_Cert_43.xls").Activate ' Change this name whenever the cert name changes (revisions) Range("B5").Select ActiveSheet.Paste Range("B5").Select Windows("AIS Certmaker Gauge List.xls").Activate ActiveWindow.Close Else Exit Sub End If End Sub

The problem I am running into is that once the name of the workbook changes (AIS_Cert_43.xls) the VB script stops working because it can no longer find the work book. The name will change because the AIS_Cert_43.xls is a template we use and the file gets saved to the current work order number.

We also revisit jobs that have been done in the past and want to be able to open the old report and update the next gage list.

I hope there is enough info here to get someone going on this. Please ask all the questions you need to.

Thanks in advance for any help that you give. This VB stuff is pretty new to me, I inherited this project from a guy that quit our company.