Ed,

Probably because that never now becomes an active workbook. Try this instead

Sub Import_Crystal1()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Ed"
fname = Application.GetOpenFilename()

If fname < "" Then

Set oWb = Workbooks.Open(fname)
oWb.Sheets("TEST11").Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste

Else
MsgBox ("Please select a Valid File")

End If
oWb.Close

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in message
...
No quite sure what is happening here but I have amended
the code and instead of copying the data in fname to sheet
Crystal_Table it is now copying the three command buttons
i have on the sheet that i run the macro for.

Sub Import_Crystal1()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Ed"
fname = Application.GetOpenFilename()

If fname < "" Then

Set oWb = Workbooks.Open(fname)
Sheets("TEST11").Select
Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste


Else
MsgBox ("Please select a Valid File")

End If
oWb.Close

End Sub


-----Original Message-----
Ed,

I don't think you can reference an active workbook via
it's full path and
name, which is what GetOpenFileName returns. Try this mod

Sub Import_Crystal()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Ed"
fname = Application.GetOpenFilename()

If fName < "" Then
Set oWb = Workbooks.Open (fname)
Sheets("TEST11").Select
Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
oWb.Close

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ed" wrote in
message
...
This is such an easy question but it just wont work for
me. I have the following code but cant seem to close the
workbook fname after i have copied the contents of a
specific sheet?

Sub Import_Crystal()

Dim fname As String


ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments
Public\Ed"
fname = Application.GetOpenFilename()

Workbooks.Open (fname)
Sheets("TEST11").Select
Range("A1:AQ100").Select
Selection.Copy


Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
Workbooks(fname).Close



End Sub


.