loop in a workbook on worksheets

Question

I am trying to loop on all the worksheets of a workbook and copy-paste in values. Here is my code:

Sub paste_hard()

    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ActiveWorkbook.Worksheets

        Set rng = ws.Range("A1:C500")
        rng.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

    Next ws

    Range("A1:C500") = rng

End Sub

Problem comes from rng.Select and I get the following error:

Method 'Select' of object 'Range' failed

I am a beginner in vba.

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

You can only select a range on an active worksheet. Your error is happening because you're trying to Select the range on sheet(s) that are not Active at runtime.

One (ugly) solution would be to put ws.Activate inside your loop (before Set rng = ...

But instead of gunking up your code with a bunch of unnecessary Select and Activate statements, do this instead -- if you're simply concerned with the values, then you don't need to do anything with Copy, Paste or PasteSpecial:

For Each ws In ActiveWorkbook.Worksheets

    Set rng = ws.Range("A1:C500")
    rng.Value = rng.Value

Next ws

And voila, 5 lines of code becomes 2 lines of code :)

edited May 23 '17 at 12:14

Community

1
1

answered Oct 16 '15 at 03:10

David Zemens

53,033
11
81
130

thanks for your help. I changed as you suggested, but 2 problems: – phacoo Oct 16 '15 at 03:17
1

You don't need the object variable and so becomes one line: `ws.Range("A1:C500") = ws.Range("A1:C500").Value` – Excel Hero Oct 16 '15 at 03:18
1 - the active sheet becomes blank (no values on it) and 2- the others worksheets stay as it is – phacoo Oct 16 '15 at 03:18
Sub paste_hard() Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets Set rng = ws.Range("A1:C500") rng.Value = rng.Value Next ws Range("A1:C500") = rng End Sub – phacoo Oct 16 '15 at 03:18
@ExcelHero arguably my method is easier to maintain, because if you ever need to redefine the range, you only need to "fix" the code in one place, instead of two, and this also makes the method easily adaptable to take the range as an argument instead of a procedure-level variable :) – David Zemens Oct 16 '15 at 03:24
@phacoo your problem may be he extra `Range("A1:C500") = rng ` before your `End Sub`. Get rid of it :) -- also, it's really really difficult to read code in the comments, so for future clarifications, it's best to revise the content of your original question, where you can format the code appropriately. Cheers. – David Zemens Oct 16 '15 at 03:26
1

@DavidZemens Yep, I would not do it either, but since you mentioned the two lines of code, I jumped in. But unless I really needed the object variable I would not create it. I would use a With block and only reference the range once. – Excel Hero Oct 16 '15 at 03:28
@ExcelHero the `With` block would be even better :) – David Zemens Oct 16 '15 at 03:29
@DavidZemens Yeah, but then we are at three lines... though could cheat and put it on one. – Excel Hero Oct 16 '15 at 03:29
Three lines, but only one object reference instead of two @ExcelHero – David Zemens Oct 16 '15 at 03:30

loop in a workbook on worksheets

1 Answers1