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.
Bookmarks