Hello all. I am trying to copy values from an unopened Excel spreadsheet to
one that I have open. I cannot figure out how to do this. When I use the
following, cells from the open sheet get selected:

Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
Range(Cells(1, 1), Cells(150, 6)).Copy

I am using Excel 2000. Please help. TIA.

Larry

Dim Wb1 As Workbook 'Workbook to copy to
Set Wb1 As ActiveWorkbook
Dim Wb2 As Workbook ' Workbook to copy from
Set Wb2 = Workbooks.Open("F:\Larry\TEST1_Backup.xls")

Wb2.Sheets("Field Sheet").Range(Cells(1, 1), Cells(150, 6)).Copy
Destination:= Wb1.Sheets("YourSheet").Range("YourRange")
Wb2.Close False

or

Wb2.Sheets("Field Sheet").Range(Cells(1, 1), Cells(150, 6)).Copy
Wb1.Sheets("YourSheet").Range("YourRange").PasteSp ecial xlPasteValues


Regards,

Alan


"vbman" wrote in message
news:yIeGh.377$3i.360@trnddc01...
Hello all. I am trying to copy values from an unopened Excel spreadsheet
to
one that I have open. I cannot figure out how to do this. When I use the
following, cells from the open sheet get selected:

Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
Range(Cells(1, 1), Cells(150, 6)).Copy

I am using Excel 2000. Please help. TIA.

Larry

Hi vbman

See
http://www.rondebruin.nl/copy7.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"vbman" wrote in message news:yIeGh.377$3i.360@trnddc01...
Hello all. I am trying to copy values from an unopened Excel spreadsheet to
one that I have open. I cannot figure out how to do this. When I use the
following, cells from the open sheet get selected:

Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
Range(Cells(1, 1), Cells(150, 6)).Copy

I am using Excel 2000. Please help. TIA.

Larry

You description is inconsistent. If you want to copy cells from the
currently open workkbook (the active book when you start the macro

set sh = Activesheet
Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
sh.Range(sh.Cells(1, 1), sh.Cells(150, 6)).Copy _
Destination:=Activesheet.range("A1")

if you want to select the cells on Field Sheet of workbook Test1_backup.xls,
it should do that now. however, you can do

Set sh = Activesheet
Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
With xlw.Sheets("Field Sheet")
.Select
.Range(.Cells(1, 1), .Cells(150, 6)).Copy _
Destination:= h.Range("A1")
End With


--
Regards,
Tom Ogilvy


"vbman" wrote in message
news:yIeGh.377$3i.360@trnddc01...
Hello all. I am trying to copy values from an unopened Excel spreadsheet
to
one that I have open. I cannot figure out how to do this. When I use the
following, cells from the open sheet get selected:

Set xlw = xl.Workbooks.Open("F:\Larry\TEST1_Backup.xls")
xlw.Activate
xlw.Sheets("Field Sheet").Select
Range(Cells(1, 1), Cells(150, 6)).Copy

I am using Excel 2000. Please help. TIA.

Larry