copy paste value best practice

[oaishm]

I wanted to copy something from one sheet to a different sheet

This is what I got, which works

set this = activeworkbook
workbooks.open filename:="file.xls"
activeworkbook.worksheets("Sheet 1").select
range(somerange).select
selection.copy
this.activate
worksheets(somesheet).select
range(somerange).select
selection.paste

Surely this is not the best practice Isnt' there something similar to:

set this = activeworkbook 'Amazingly, Me doesn't work
dim openfile = new workbooks
openfile.open filename:="file.xls"
openfile.worksheets("Sheet 1").Range(somerange).copy
this.worksheets("Sheet 1").Range(somerange).paste

Also, am I doing something wrong with the set command. I thought if I do:
set book = workbooks("larry")

I could do book.range(somernage)

and if I do
set somerange = range(soemrange)

then I can do

somerange.values = "soemthing"
somerange.select
etc.

and if I have a name such as "newname" defined in the worksheet, thsi works:

worksheets(someworksheet).range("newname")

but this doesn't

worksheets(someworksheet).newname

Am I missing something fundamental? The way I'm using this language is really verbose

 

[Jonmo1]

put the copy and paste all in one line

openfile.worksheets("Sheet 1").Range(somerange).copy Destination:= this.worksheets("Sheet 1").Range(somerange)

Hope that helps.

 

[oaishm]

But, that's my point

If you do:

dim openfile as worksheets
openfile.open filename:=soemfile

openfile.worksheets("sheet1") doesn't work because worksheets isn't a datafile or member. so instead, you've got to do
openfile.activate
worksheets("sheet1").select
range(somerange).select
etc...

 

[Jonmo1]

Try

Dim openfile As Workbook
Set openfile = Workbooks.Open(Filename:="file.xls")
openfile.Worksheets("Sheet 1").Range(somerange).Copy Destination:=this.Worksheets("Sheet 1").Range