I have had the following code kindly supplied by arlu1201 which I have been using to great effect. I now have a question regard the ClearContents function:

Option Explicit

Sub cons_sheets()
Dim ws As Worksheet
Dim lrow As Long, lrow1 As Long

Application.ScreenUpdating = False

If Not Evaluate("ISREF(Summary!A1)") Then
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Summary"
Else
    Worksheets("Summary").Cells.ClearContents
End If
Worksheets("Summary").Range("C1:I1") = Split("Date,Description, Action,Operative,Hours,Complete,Sheet", ",")

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        lrow = ws.Range("C" & Rows.Count).End(xlUp).Row
        ws.Range("C7:H" & lrow).Copy Worksheets("Summary").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
        lrow = Worksheets("Summary").Range("I" & Rows.Count).End(xlUp).Row
        lrow1 = Worksheets("Summary").Range("C" & Rows.Count).End(xlUp).Row
        Worksheets("Summary").Range("I" & lrow + 1 & ":I" & lrow1).Value = ws.Name
    End If
Next ws

Application.ScreenUpdating = True

End Sub
Is it possible to clear only certain cells as opposed to the whole worksheet? The reason I ask is that if I have any other formula anywher on the sheet this gets wiped out every time I run the macro.

Thanks in advance.