Avoid Runtime Error on Range Selection from WS already declared and set

Question

I have this code and it is working fine if I run the macro while Sheet1 is selected or activated.
However it fails when other sheets are selected. Why?
I get a:

Runtime Error: Select method of range class failed

Isn't it covered when I Set ws = ThisWorkbook.Sheets("Sheet1")

Sub test()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:M" & lrow).Select
    End With
End Sub

How can I improve the code and avoid the Runtime Error?
What am I missing?
Any help would be much appreciated.

Why do you want to select the range? Why not directly work with it? [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Oct 11 '13 at 06:49
i did that actually :) problem is i need to preserve the data format of the values. so i try the select and do the paste special. is there a way to directly copy and paste to ranges and preserve data format? — L42, Oct 11 '13 at 07:03
Yup there is. Where do you want to copy `.Range("A1:M" & lrow)` to? — Siddharth Rout, Oct 11 '13 at 07:04
to itself. it contains formula, so i want to preserve the data format of values i get from the formula and paste it as values. — L42, Oct 11 '13 at 07:07

Siddharth Rout · Accepted Answer · 2013-10-11T07:15:13.517

1

to itself. it contains formula, so i want to preserve the data format of values i get from the formula and paste it as values. – L42 1 min ago

Like I mentioned, you can avoid the use of .Select in most of the cases.

Is this what you are trying?

Replace

.Range("A1:M" & lrow).Select

with

.Range("A1:M" & lrow).Value  = .Range("A1:M" & lrow).Value

Or maybe this?

Sub test()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:M" & lrow).Copy
        .Range("A1:M" & lrow).PasteSpecial xlPasteValues
    End With
End Sub

edited Oct 11 '13 at 07:15

answered Oct 11 '13 at 07:08

Siddharth Rout

147,039
17
206
250

yeah, that's what i'm using. unfortunately i have a long number in a form of string (value i get from the formula). that code changes that value into numbers instead of text. – L42 Oct 11 '13 at 07:11
Can you give me an example of that string? so I can test it before updating the above post – Siddharth Rout Oct 11 '13 at 07:14
any long number.(eg. 250000326959205128). – L42 Oct 11 '13 at 07:16
haha it works as usual. that argument doesn't came out when i first tried it. i used '(' as oppose to hitting space. LOL. – L42 Oct 11 '13 at 07:29

Avoid Runtime Error on Range Selection from WS already declared and set

1 Answers1