run-time error '1004' on range selecting

Question

I get run-time error '1004' on this code
sh2 is a specific sheet on another file. I don't want to activate the second sheet,just use it and then closing the file. I get my error on the else condition

If sh2.Range(sh2.Cells(1, y + 4), sh2.Cells(1, y + 4)) <> "" Then
                        y = y + 1
Else: sh2.Range(sh2.Cells(1, 4), sh2.Cells(114, (y + 4) - 1)).Select
                        Selection.Copy

When I'm trying to debug: the y is 49 (as it had to be) and the cells of the range under the else get their own and right values. Where is my error?

PS. I'm using excel 2013 and the y is set to 0 before this code

Please avoid the use of `.Select/.Activate` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Feb 06 '14 at 20:48

score 1 · Answer 1 · answered Feb 02 '14 at 12:27

1

try sh2.Range(sh2.Cells(1, 4), sh2.Cells(114, (y + 4) - 1)).Copy (without selecting) i think you cannot select anything on the sheet unless you activate it

answered Feb 02 '14 at 12:27

kamila

74
6

score 1 · Answer 2 · answered Feb 02 '14 at 12:27

Your original code worked for me - what happens if you drop the Select?

Dim sh2 As worksheet
Set sh2 = Sheets(2)

y = 49
If sh2.Range(sh2.Cells(1, y + 4), sh2.Cells(1, y + 4)) <> "" Then
    y = y + 1
Else
    sh2.Range(sh2.Cells(1, 4), sh2.Cells(114, (y + 4) - 1)).Copy
End If

score 0 · Answer 3 · answered Feb 02 '14 at 11:42

0

I think if you drop the sh2. from within the sh2.range it will work.

Change sh2.Range(sh2.Cells(1, y + 4), sh2.Cells(1, y + 4)) to

sh2.Range(Cells(1, y + 4), Cells(1, y + 4))

Untested though

answered Feb 02 '14 at 11:42

Mark Fitzgerald

3,048
3
24
29

1

the error is on the range under the else, the first range is ok – user3023582 Feb 02 '14 at 11:59

run-time error '1004' on range selecting

3 Answers3