Hi

I have the following code which should copy values in a
sheet and then paste them into a new sheet but it comes up
with the error 'Subscript not in range'

Does anyone have any ideas why this is happening?

Thanks

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)
Windows(fname).Activate
Sheets("TEST11").Select
Range("A1:AQ100").Select
Selection.Copy
Windows("Remittance Procedure").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
End Sub

Ed,

Is it this line

Windows(fname).Activate

If so, just delete it. It is unnecessary as the newly opened workbook
becomes the activeworkbook by default.

By the way, the erros is caused by the fact that fName is a full path name,
not just the workbook name.

--

HTH

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

"Ed" wrote in message
...
Hi

I have the following code which should copy values in a
sheet and then paste them into a new sheet but it comes up
with the error 'Subscript not in range'

Does anyone have any ideas why this is happening?

Thanks

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)
Windows(fname).Activate
Sheets("TEST11").Select
Range("A1:AQ100").Select
Selection.Copy
Windows("Remittance Procedure").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
End Sub