I am having problems with the following code.

It comes up with the error Subscript out of range - I know
this can be caused when you are refering to the workbooks
full path instead of the name but I can see this happening
here.

Does anyone have any ideas?

'Get file path for import
ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Ed"
fname = Application.GetOpenFilename()

'Check that a file was selected
If fname < "" Then
Range("a1").Value = fname
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

Which line is highlighted when you get the error?

You would get the error if you don't have a sheet named TEST11 in the
workbook you just opened.

if you don't have a workbook opened named: Remittance Procedure.xls

if Remittance Procedure.xls does not contain a sheet named Crystal_Table

Before you say you do, check to make sure you don't have two spaces in the
Remittance book, and don't have spaces the end of TEST11 or Crystal_Table.

--
Regards,
Tom Ogilvy


"Edgar Thoemmes" wrote in message
...
I am having problems with the following code.

It comes up with the error Subscript out of range - I know
this can be caused when you are refering to the workbooks
full path instead of the name but I can see this happening
here.

Does anyone have any ideas?

'Get file path for import
ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Ed"
fname = Application.GetOpenFilename()

'Check that a file was selected
If fname < "" Then
Range("a1").Value = fname
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

Hi Tom

I did have a space after the Crystal_Table so I no longer
get the Subscript out of range Error but it is not copying
the data from 'Test11' to 'Crystal_table' it is opening
the book but its just not copying the data.

Thanks


-----Original Message-----
Which line is highlighted when you get the error?

You would get the error if you don't have a sheet named
TEST11 in the
workbook you just opened.

if you don't have a workbook opened named: Remittance
Procedure.xls

if Remittance Procedure.xls does not contain a sheet
named Crystal_Table

Before you say you do, check to make sure you don't have
two spaces in the
Remittance book, and don't have spaces the end of TEST11
or Crystal_Table.

--
Regards,
Tom Ogilvy


"Edgar Thoemmes"
wrote in message
...
I am having problems with the following code.

It comes up with the error Subscript out of range - I
know
this can be caused when you are refering to the
workbooks
full path instead of the name but I can see this
happening
here.

Does anyone have any ideas?

'Get file path for import
ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments
Public\Ed"
fname = Application.GetOpenFilename()

'Check that a file was selected
If fname < "" Then
Range("a1").Value = fname
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


.

Sorry Ignore last posting.

This is all fine now.

Edgar


-----Original Message-----
Which line is highlighted when you get the error?

You would get the error if you don't have a sheet named
TEST11 in the
workbook you just opened.

if you don't have a workbook opened named: Remittance
Procedure.xls

if Remittance Procedure.xls does not contain a sheet
named Crystal_Table

Before you say you do, check to make sure you don't have
two spaces in the
Remittance book, and don't have spaces the end of TEST11
or Crystal_Table.

--
Regards,
Tom Ogilvy


"Edgar Thoemmes"
wrote in message
...
I am having problems with the following code.

It comes up with the error Subscript out of range - I
know
this can be caused when you are refering to the
workbooks
full path instead of the name but I can see this
happening
here.

Does anyone have any ideas?

'Get file path for import
ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments
Public\Ed"
fname = Application.GetOpenFilename()

'Check that a file was selected
If fname < "" Then
Range("a1").Value = fname
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


.