setting range fails

[cedarhay]

Hi all,

I suspect something fairly obvious, but can't see it.

With the following snippet
Dim destrange As Range
dim destsh as worksheet
Set destsh = Workbooks(myfilename).Sheets(1)
..some code
a) Set destrange = destsh.Range("a11:d19")
b) Set destrange = destsh.Range(Cells(11, 1), Cells(19,4))

Why does (a) work, and (b) fail with messages
"Runtime error '1004'
Method 'Range' of object '_Worksheet' failed


Many thanks

 

[poolhall]

both from your example work for me.

 

[Jonmo1]

When you specify the sheet on the Range, you must also specify it on both Cells within the range...

Try

Set destrange = destsh.Range(destsh.Cells(11, 1), destsh.Cells(19,4))


Hope that helps.

 

[cedarhay]

Thanks for the tip, unfortunately it didn't work

I found that if I activated the workbook first the code worked ok