Importing data

E

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
 
T

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.
 
E

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closing workbook 4
Subscript out of range 1
Import Wizard 1
Copy - Paste 4
Import Worksheets and "Overright" 9
Copy Sheets 0
VBA - Importing Sheets 18
Export/Import in CSV 2

Top