VBA Edit range on multiple worksheets

0
votes

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!

3
vbaexcelspreadsheet
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
Did not notice there are comments on answers. That solved my problem. Thank you! – Gatiivs

3 Answers

3
votes

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
1
votes

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"
0
votes

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