Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not doing what I hoped for
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not doing what I hoped for
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not doing what I hoped for
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not doing what I hoped for
Thanks
Any way that can be made to return me to the worksheet and cell were the code was triggered? "Mike H" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
RANK not resulting as hoped | Excel Discussion (Misc queries) | |||
Not getting hoped for result | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |