Clear all including objects without deleting sheets

[Curt]

I would like a macro that uses the "EDIT" - "CLEAR ALL" command for all
sheets that do not have colored tabs. I would also like it to remove all
charts, objects, images, etc. for all sheets that do not have colored tabs.

Currently, I can only remove all objects by manually deleting each object
one at a time. Deleting the sheets is not an option because that results in
cell refrences for me as I refrence cells in the sheets that I am trying to
clear.

Any help is greatly appreciated!

Curt J

 

[Don Guillett]

try this
Sub clearall()
On Error Resume Next
For Each ws In Worksheets
ws.Cells.Clear
For Each sh In ws.Shapes
sh.Cut
Next sh
Next ws
End Sub

 

[JLGWhiz]

this worked pretty good.

Sub dk()
For Each sh In ThisWorkbook.Sheets
If sh.Tab.ColorIndex = xlColorIndexNone Then
sh.Cells.Clear
For i = sh.Shapes.Count To 1 Step -1
sh.Shapes(i).Delete
Next
End If
Next
End Sub

 

[Gord Dibben]

Sub clear_objects()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Tab.ColorIndex = xlNone Then
With ws
.DrawingObjects.Delete
.Cells.Clear
End With
End If
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Chip Pearson]

Try code like the following:

Sub AAA()
Dim WS As Worksheet
Dim OleObj As OLEObject
Dim SH As Shape

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
If WS.Tab.ColorIndex > 0 Then
For Each OleObj In WS.OLEObjects
OleObj.Delete
Next OleObj
For Each SH In WS.Shapes
SH.Delete
Next SH
End If
Next WS
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

The code previously posted clears the objects from sheet with colored
tabs, rather than uncolored tabs. Use this code instead (the '<' is
flipped).

Sub AAA()
Dim WS As Worksheet
Dim OleObj As OLEObject
Dim SH As Shape

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
If WS.Tab.ColorIndex < 0 Then
For Each OleObj In WS.OLEObjects
OleObj.Delete
Next OleObj
For Each SH In WS.Shapes
SH.Delete
Next SH
End If
Next WS
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Curt]

I thank all of you for the help. The replies were great!