Can anyone tell me why the code below only works on the worksheet that's
active when it is run? My hope was that it would go to each worksheet
(excluding Holidays) and remove all shapes and clear the value in cell F1

Sub HolidayRemove()

Protection.UnProtectAllSheets

For n = 1 To Sheets.Count
If Sheets(n).Name <> "Holidays" Then
With Sheets(n)

ActiveSheet.Shapes.SelectAll
Selection.Delete
Range("K1").Value = ""

End With
End If

Next n

Protection.ProtectAllSheets
End Sub

Hi,

Try this but be aware

ActiveSheet.Shapes.SelectAll
Selection.Delete

is a very blunt instrument and may delete more than you bargained for. try
it on a test workbook.


Sub HolidayRemove()
For n = 1 To Sheets.Count
If Sheets(n).Name <> "Holidays" Then
Sheets(n).Select
Sheets(n).Unprotect
With Sheets(n)
ActiveSheet.Shapes.SelectAll
Selection.Delete
Range("K1").Value = ""
Sheets(n).Protect
End With
End If
Next n
End Sub


Mike

"Patrick C. Simonds" wrote:

> Can anyone tell me why the code below only works on the worksheet that's
> active when it is run? My hope was that it would go to each worksheet
> (excluding Holidays) and remove all shapes and clear the value in cell F1
>
> Sub HolidayRemove()
>
> Protection.UnProtectAllSheets
>
> For n = 1 To Sheets.Count
> If Sheets(n).Name <> "Holidays" Then
> With Sheets(n)
>
> ActiveSheet.Shapes.SelectAll
> Selection.Delete
> Range("K1").Value = ""
>
> End With
> End If
>
> Next n
>
> Protection.ProtectAllSheets
> End Sub
>
>

untested, but give it a try

Sub HolidayRemove()

Dim n As Long

For n = 1 To Sheets.Count
If Sheets(n).Name <> "Holidays" Then
With Sheets(n)
.Activate
.Unprotect
.Shapes.SelectAll
Selection.Delete
.Range("K1").Value = ""
.Protect
End With
End If

Next n


End Sub


--


Gary

"Patrick C. Simonds" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can anyone tell me why the code below only works on the worksheet that's
> active when it is run? My hope was that it would go to each worksheet
> (excluding Holidays) and remove all shapes and clear the value in cell F1
>
> Sub HolidayRemove()
>
> Protection.UnProtectAllSheets
>
> For n = 1 To Sheets.Count
> If Sheets(n).Name <> "Holidays" Then
> With Sheets(n)
>
> ActiveSheet.Shapes.SelectAll
> Selection.Delete
> Range("K1").Value = ""
>
> End With
> End If
>
> Next n
>
> Protection.ProtectAllSheets
> End Sub

Thanks

Any way that can be made to return me to the worksheet and cell were the
code was triggered?


"Mike H" <(E-Mail Removed)> wrote in message
news:757B60F1-55D1-4EE8-A84F-(E-Mail Removed)...
> Hi,
>
> Try this but be aware
>
> ActiveSheet.Shapes.SelectAll
> Selection.Delete
>
> is a very blunt instrument and may delete more than you bargained for. try
> it on a test workbook.
>
>
> Sub HolidayRemove()
> For n = 1 To Sheets.Count
> If Sheets(n).Name <> "Holidays" Then
> Sheets(n).Select
> Sheets(n).Unprotect
> With Sheets(n)
> ActiveSheet.Shapes.SelectAll
> Selection.Delete
> Range("K1").Value = ""
> Sheets(n).Protect
> End With
> End If
> Next n
> End Sub
>
>
> Mike
>
> "Patrick C. Simonds" wrote:
>
>> Can anyone tell me why the code below only works on the worksheet that's
>> active when it is run? My hope was that it would go to each worksheet
>> (excluding Holidays) and remove all shapes and clear the value in cell F1
>>
>> Sub HolidayRemove()
>>
>> Protection.UnProtectAllSheets
>>
>> For n = 1 To Sheets.Count
>> If Sheets(n).Name <> "Holidays" Then
>> With Sheets(n)
>>
>> ActiveSheet.Shapes.SelectAll
>> Selection.Delete
>> Range("K1").Value = ""
>>
>> End With
>> End If
>>
>> Next n
>>
>> Protection.ProtectAllSheets
>> End Sub
>>
>>