Help with simple clearcontents loop function through worksheets

[nanook3890]

Hi All,

Can anyone tell me why this code doesn't work? It only clears the active sheet so I know it's something to do with how I use the Worksheets/workbook bit.

All I need to do is clear the contents of the below range on every sheet in the workbook. Can anyone help please? Thanks so much in advance!


Sub Clearcontents()


Dim HolidayDates As Range
Dim wWorksheet As Worksheet




Set HolidayDates = Range("C19:E34")


With Workbooks("Annual Leave 2015.xlsx")

For Each wWorksheet In ActiveWorkbook.Worksheets

HolidayDates.Clearcontents

Next wWorksheet
End With


End Sub

 

[VoG]

Try

Sub Clcontents()
Dim ws As Worksheet
For Each ws In Workbooks("Annual Leave 2015.xlsx").Worksheets
    ws.Range("C19:E34").ClearContents
Next ws
End Sub

 

[nanook3890]

Try

Sub Clcontents()
Dim ws As Worksheet
For Each ws In Workbooks("Annual Leave 2015.xlsx").Worksheets
    ws.Range("C19:E34").ClearContents
Next ws
End Sub

Amazing - thank you so much! Super concise and works perfectly.