Copy Data From a Closed Workbook

[RichardBerry]

HI

I am trying to copy a range of data from a closed workbook into the currently active Workbook

This is the code I am trying to use, but get an error "Subscript Out Of Range" as soon as I refer to the external Workbook.

Please Login or Register  to view this content.

The external Workbook and Sheet definitely do exist and are spelled correctly

Any help will be appreciated

Cheers

Richard

[Simon Lloyd]

Why not open it first?

Please Login or Register  to view this content.

[RichardBerry]

Thanks for the reply Simon

No problem to open the Workbook first.

I tried the code you suggest, the book opens fine, but I still get "Subscript Out Of Range" Error
on the following line:

Please Login or Register  to view this content.

I also tried 'Sheets(1)' and get the same error.

[Simon Lloyd]

Did you get "Subscript out of range error 9"
Try this

Please Login or Register  to view this content.

[Simon Lloyd]

What exactly is the range of data is it from A7 to the last row of data in column a that you are copying? do you want to paste all that data to the next available row in the destination workbook?

[RichardBerry]

Hi Simon

The data in the Lookup Book consists of about 20 rows (but may expand) and 3 columns. It contains a code, a labor rate and a description of the labor rate.

I will add code for the other columns once I get one column working...

I tried opening the lookup book, selecting the sheet, as you suggested, and reading the data into an array. Then closing the lookup book, and writing the array data to the current book.

This is the code I tried, but I get a Subscript Out Of Range (Error 9) on the line:Results(row) = Cells(row, 1)

Please Login or Register  to view this content.

[RichardBerry]

Hi Simon

I am replying to my own post coz I cannot see how to reply to yours a second time.

Thanks for your help.

The problem was I used the full path in Workbooks.Open("C:\Masterlookup")

When referring to cells in the Workbook, you must only use the name of the Workbook - i.e.

Cells(1,1) = Workbooks("Masterlookup.xls").Sheets("lookup_table").Cells(1,1)

WITHOUT the path...

Thanks For your help!!

[Simon Lloyd]

Glad you are sorted i should have noticed that!