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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Curt" <(E-Mail Removed)> wrote in message
news:CB657971-5500-45BD-B7DA-(E-Mail Removed)...
>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

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



"Curt" <(E-Mail Removed)> wrote in message
news:CB657971-5500-45BD-B7DA-(E-Mail Removed)...
>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

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

On Thu, 15 Apr 2010 10:29:02 -0700, Curt <(E-Mail Removed)>
wrote:

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



On Thu, 15 Apr 2010 10:29:02 -0700, Curt
<(E-Mail Removed)> wrote:

>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

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



On Thu, 15 Apr 2010 10:29:02 -0700, Curt
<(E-Mail Removed)> wrote:

>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

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

"Gord Dibben" wrote:

> 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
>
> On Thu, 15 Apr 2010 10:29:02 -0700, Curt <(E-Mail Removed)>
> wrote:
>
> >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
>
> .
>