Sheet selection

[Patrick Simonds]

Is there a way to amend this code so that it does not actually make the
sheet (in this case September) active?

Sub September_Set()

Sheets("September").Select

Range("D4:F13").Select
Selection.Value = ""
Selection.Offset(0, 26).Value = ""

Range("D15:F25").Select
Selection.Value = ""
Selection.Offset(0, 26).Value = ""

Range("A2").Select

End Sub

 

[Guest]

Avoid Selections and stay where you are:


Sub September_Set()
Dim ws As Worksheet
Set ws = Worksheets("September")
With ws

.Range("D4:F13").Value = ""
.Range("D4:F13").Offset(0, 26).Value = ""


.Range("D15:F25").Value = ""
.Range("D15:F25").Offset(0, 26).Value = ""

End With
End Sub

 

[Tom Ogilvy]

Sub September_Set()

With Sheets("September")

With .Range("D4:F13")
.ClearContents
.Offset(0, 26).ClearContents
End With
With .Range("D15:F25")
.ClearContents
.Offset(0, 26).ClearContents
End With
End With
End Sub

 

[Patrick Simonds]

Can the following also be done without selecting the sheet?

If Worksheets("November").Range("O39").Value =
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M40").Value = "Thanksgiving"
Worksheets("November").Range("M41").Value = "Day"

Range("A2:C2").Select

End If

If Worksheets("November").Range("O51").Value =
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M52").Value = "Thanksgiving"
Worksheets("November").Range("M53").Value = "Day"

Range("A2:C2").Select

End If

 

[Tom Ogilvy]

the selecting in that code is superfluous

If Worksheets("November").Range("O39").Value = _
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M40").Value = "Thanksgiving"
Worksheets("November").Range("M41").Value = "Day"
End If

If Worksheets("November").Range("O51").Value = _
Worksheets("November").Range("W1").Value Then
Worksheets("November").Range("M52").Value = "Thanksgiving"
Worksheets("November").Range("M53").Value = "Day"
End If

should work fine.