Importing data

[Edgar Thoemmes]

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

 

[Tom Ogilvy]

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.

 

[Edgar Thoemmes]

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

 

[Edgar Thoemmes]

Sorry Ignore last posting.

This is all fine now.

Edgar