For Each ws within ActiveWorkbook not working

[thardin]

What's wrong with this code?
This code was working just fine until I tried doing this on each ws.

Is it the .select that's messing things up.

When I run this code it does this at the end of the worksheet:

Sub UBTSignoff()
'
' UBTSignoff Macro
'
' Keyboard Shortcut: Ctrl+f
'
 Dim strFullDate As String
 Dim Ws As Worksheet
 
 strFullDate = Format(Now, "mm/dd/yy")
 

For Each Ws In Worksheets
        Range("B" & Rows.Count).End(xlUp).Offset(3).Select
        ActiveCell.FormulaR1C1 = "Agreed to cancel all."
        Range("B" & Rows.Count).End(xlUp).Offset(1).Select
        ActiveCell.FormulaR1C1 = "Completed by TCH " & strFullDate
Next


End Sub

 

[GWteB]

You're iterating the worksheets collection without activating each worksheet so the .Select method fails. Activating or selecting anything is almost never necessary, so try this ...

Sub UBTSignoff()
    '
    ' UBTSignoff Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    Dim strFullDate As String
    Dim Ws As Worksheet
 
    strFullDate = Format(Now, "mm/dd/yy")
 

    For Each Ws In Worksheets
        Ws.Range("B" & Rows.Count).End(xlUp).Offset(3).Value = "Agreed to cancel all."
        Ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "Completed by TCH " & strFullDate
    Next

End Sub

 

[thardin]

You're iterating the worksheets collection without activating each worksheet so the .Select method fails. Activating or selecting anything is almost never necessary, so try this ...

Sub UBTSignoff()
    '
    ' UBTSignoff Macro
    '
    ' Keyboard Shortcut: Ctrl+f
    '
    Dim strFullDate As String
    Dim Ws As Worksheet
 
    strFullDate = Format(Now, "mm/dd/yy")
 

    For Each Ws In Worksheets
        Ws.Range("B" & Rows.Count).End(xlUp).Offset(3).Value = "Agreed to cancel all."
        Ws.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "Completed by TCH " & strFullDate
    Next

End Sub

Worked great! thanks

 

[GWteB]

You are welcome and thanks for the feedback.