what mistake Am I doing?

sheet1.activate
activesheet.range(cells(2,"Q"),cells(40,"K")).select 'here the error occurs 
selection.copy

The abo开发者_StackOverflowve code works for somtimes and for sometimes throws an Error that with object variable not set what does that mean ? why the above code is not working and if i reopen the file it works again and sometimes dont. But i dont know the reason why it happens. Am i making any mistake in the syntax, If so please let me know the right way to do it. And is there any more efficient way to do to select a set of range ? other than the above ? Thank you in advance

The cause of this error is usually the omission of a Set keyword, e.g.

Dim w as Worksheet
w = Sheet1 ' throws error 91
' Correct syntax: 
' Set w = Sheet1 

If you tell us on what line the error occurs, then a more specific answer will be forthcoming. The syntax of your code sample looks fine, but the error may be due to another line in your code, perhaps not shown in your question.

Is there any more "efficient" way to do to select a range? Personally, I don't like the method you show, with strings as the arguments for Cells. Strings are messy, and often lead to mistakes and therefore a loss of work efficiency. Why not:

Sheet1.Range(Cells(2,18),Cells(40,11)).Select
' or
Sheet1.Cells(2,11).Resize(39,8).Select
' or
Sheet1.Range("K2").Resize(39,8).Select

Or, define K2:Q40 as a named range in your sheet, calling it e.g. "myRange", and do this:

Sheet1.Range("myRange").Select ' My personal favourite

But why are you using Select? This is usually not necessary. Just write:

Sheet1.Range("myRange").Copy

But then I could ask, why are you using Copy, which is a messy method in its own right?...