VBA to reference from worksheet names and insert value into columns

[Neveidas]

Hello,

I am trying to use VBA to fill an entire column with the value of choice if the worksheet name is right, and after the column is filled, it will proceed to the next worksheet and do the same. I am unsure
if I am writing this correctly, greatly appreciate it if anyone is able to advise or help correct the codes!

Private Sub Fill()
Dim WS As Worksheet

Dim i As Long
Dim r As Range


i = Range("B" & Rows.Count).End(xlUp).Row
Set r = Range("A2:A" & i)

For Each WS In ActiveWorkbook.Worksheets

If WS.Name = "Update(Pre)" Or WS.Name = "Update XB(Pre)" Then r.Value = "Update(Pre)"
If WS.Name = "Update(Breach)" Or WS.Name = "Update XB(Breach)" Then r.Value = "Update(Breach)"
If WS.Name = "Lost(Pre)" Or WS.Name = "Lost XB(Pre)" Then r.Value = "Lost(Pre)"
If WS.Name = "Lost(Breach)" Or WS.Name = "Lost XB(Breach)" Then r.Value = "Lost(Breach)"

Next WS





End Sub

 

[My Aswer Is This]

I would do it this way:

Sub Fill()
'Modified  10/6/2021  5:40:58 AM  EDT
Application.ScreenUpdating = False
Dim WS As Worksheet
Dim i As Long
Dim r As Range

    For Each WS In ActiveWorkbook.Worksheets

        i = WS.Range("B" & Rows.Count).End(xlUp).Row
        Set r = WS.Range("A2:A" & i)
        
        Select Case WS.Name
            Case "Update(Pre)", "Update XB(Pre)": r.Value = "Update(Pre)"
            Case "Update(Breach)", "Update XB(Breach)": r.Value = "Update(Breach)"
            Case "Lost(Pre)", "Lost XB(Pre)": r.Value = "Lost(Breach)"
            Case "Lost(Breach)", "Lost XB(Breach)": r.Value = "Lost XB(Breach)"
        End Select

    Next WS
Application.ScreenUpdating = False
End Sub

 

[Alex Blakenburg]

@My Aswer Is This - just need to change the last line to True (I left the statement out altogether recently so it is still fresh in my mind)

 

[My Aswer Is This]

@My Aswer Is This - just need to change the last line to True (I left the statement out altogether recently so it is still fresh in my mind)

Not sure what your saying:
Change last line to True??

 

[My Aswer Is This]

Not sure what your saying:
Change last line to True??

OK I see what you mean.
You can change that correct
I see again your not the original Poster

 

[My Aswer Is This]

In my script change last line of code to say "True" not "False"

 

[Neveidas]

Thank you guys so much! it worked like a charm~

 

[My Aswer Is This]

Thank you guys so much! it worked like a charm~

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.