Clear all including objects without deleting sheets

  • Thread starter Thread starter Curt
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top