Excel-Importing data from one workbook to another?

[pingwin77]

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:

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.

[westconn1]

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

try to avoid using active sheet or selection, change the code to something like

workbooks("AIS Certmaker Gauge List.xls").sheets("sheet1").Range("B5:F300").copy workbooks("AIS_Cert_43.xls").sheets("sheet1").range("b5)

change sheet names to suit your workbooks