VBA Edit range on multiple worksheets

Question

So I am trying to edit Range on all worksheets except one. So far I came up with this

Sub Test2()

    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name <> "Summary" Then
            Range(Cells(1, 3), Cells(1, 3)) = "test"
            MsgBox WS.Name
        End If
    Next WS

End Sub

It was supposed to put "test" on every worksheet not named Summary in cell C3. Instead it just adds "test" to the active sheet and none of the others. The message box is working fine though. The full project includes editing some cells on each sheet based on a string lenght condition, however I am stuck on editing range on multiple sheets.

Help will be appreciated!

Meant to say C1. While @Thomas example worked with: Range("C3") = "test" it did not work with .Range(Cells(1, 3), Cells(1, 3)) = "test" It showed Method 'Range' of object '_Worksheet' Failed It is crucial that it is in cells form so I can later use variables for the range. — Gatiivs, Jul 26 '16 at 08:11
Did not notice there are comments on answers. That solved my problem. Thank you! — Gatiivs, Jul 26 '16 at 08:21

score 3 · Accepted Answer · answered Jul 26 '16 at 07:28

You need to qualify the references to the worksheet WS. Use Range(Cells(1, 3), Cells(1, 3)) to reference a group of cells. Use WS.Range("C3") or WS.Cells(3,3) instead.

Sub Test2()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> "Summary" Then

                .Range("C3") = "test"
                MsgBox .Name

            End If
        End With
    Next ws

End Sub

Anastasiya-Romanova 秀 · Answer 2 · 2016-07-26T09:19:21.680

1

Just change the statement Range(Cells(1, 3), Cells(1, 3)) to

WS.Range("C1") = "test"

then it will work fine. Alternatively, you may use

WS.[C1] = "test"

edited Jul 26 '16 at 09:19

answered Jul 26 '16 at 07:39

Anastasiya-Romanova 秀

3,326
28
48

3

He has to qualify the cells in the range as well `WS.Range(WS.Cells(1, 3), WS.Cells(1, 3)) = "test"` or it'll throw an `Error '1004' Method 'Range' of object '_Worksheet' failed`. – Jul 26 '16 at 07:44
@ThomasInzina The OP has already written the full code except he didn't write `WS.` before the range. That's why I said just add `WS.` before it so he doesn't have to rewrite his code. – Anastasiya-Romanova 秀 Jul 26 '16 at 07:46
Yes @ThomasInzina. It is throwing a error `Run-Time error '1004' and the comment you have given resolved the issue – mulla Jul 26 '16 at 07:47
@mulla I put the code in ThisWorkbook module and it works fine for me. – Anastasiya-Romanova 秀 Jul 26 '16 at 07:48
@Anastasiya-Romanova秀:We need to even add WS inside before the cell tag as well and without that it haven't worked. That is the comment that Thomas inzina has added. I am getting a error in my machine with the lines which you mentioned – mulla Jul 26 '16 at 07:49
@Anastasiya-Romanova秀 put that code in your workbook, then open a different sheet than "WS" and run the code. It _will_ give you an error because the `Cells` called within the range will reference the Active Sheet, but the `Range` is referencing WS – RGA Jul 26 '16 at 07:51
1

Try this `Sheet2.Activate: Sheet1.Range(Cells(1,1),Cells(1,1)) = 10` – Jul 26 '16 at 07:51
@mulla I copy what the OP code and put it in Module1 or ThisWookbook and it works fine. I did try the code before posting the answer – Anastasiya-Romanova 秀 Jul 26 '16 at 07:52
@Anastasiya-Romanova秀 unfortunately, you didn't try thoroughly enough. If the active sheet is not the same as WS, your code **will** error – RGA Jul 26 '16 at 07:56
@Anastasiya-Romanova秀: But somehow i am getting a error and with the extra WS before the `cell` tag resolve the issue. I will try to figure out how it worked out. – mulla Jul 26 '16 at 07:56
@RGA I did try what are you telling me but seriously, it works for me. I didn't lie – Anastasiya-Romanova 秀 Jul 26 '16 at 07:58
@Anastasiya-Romanova秀 See [this question](http://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) to understand the point that Thomas and I are trying to make for you – RGA Jul 26 '16 at 08:00
@RGA Ahh, I see your point. I only used `WS.[C1]` when running it. I'm really sorry. Should I delete this answer? Or I just edit it? – Anastasiya-Romanova 秀 Jul 26 '16 at 09:09
If you edit it to remove the error and still feel it contributes something different than the other answers here, feel free to leave it posted – RGA Jul 26 '16 at 09:17

score 0 · Answer 3 · answered Jul 26 '16 at 07:54

The method Thomas shows is sound in looping through each worksheet, I would though like to highlight a method for if you had multiple sheets containing the name "Summary". As you cant have multiple sheets with the same name, they would have to be independently named, such as "Summary 1", "Summary 2", etc...

This can be tested with the InStr() function, to see if there is the string Summary within the name of the sheet:

Sub Test2()
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If InStr(1, .Name, "Summary") = False Then
                .Range("C3") = "test"
                MsgBox .Name
            End If
        End With
    Next ws
End Sub

VBA Edit range on multiple worksheets

3 Answers3