Delete a range on all worksheets but 1

[Juenapa]

Hi,

I'm trying very hard to Delete a range on all worksheets but 1.

I have a code that protects all sheets and I'm trying to edit it to delete a range on all worksheets but 1. I've changed the "if" line to read:

If objSheet.Sheet2.Name = True Then objSheet.Cells.Range("a1:j9").ClearContents

Where am I going wrong? Or should I being going a different way.

Thanks


Working code

"Public Sub aProtectAllSheets()

Dim objSheet As Worksheet

'Protecting all worksheets with password
For Each objSheet In Worksheets
If objSheet.ProtectContents = False Then objSheet.Protect "abc"
Next objSheet

End Sub"

 

[hstaubyn]

Hi Juenapa,

try something like this:

Option Explicit
 
Sub ClearA1toJ9()
 
Dim ws As Worksheet
Dim TheName As String
 
TheName = Application.InputBox("Type name of sheet NOT to clear cells A1:J9 in", , "Sheet2")
For Each ws In ActiveWorkbook.Worksheets
    If Not ws.Name = TheName Then
        ws.Range("A1:J9").ClearContents
    End If
Next ws
 
End Sub

 

[Juenapa]

I now have this code that is working, just need to omit a sheet from doing all sheets.
Was thinking maybe something like "If Not".

Sub ClearAll()
Dim Sh As Worksheet
On Error Resume Next
For Each Sh In Worksheets
Sh.Range("a1:J34").ClearContents
Next
End Sub

 

[Jonmo1]

Try

If Sh.Name <> "Sheet Name to Omit" Then
    Sh.Range("a1:J34").ClearContents
End If

 

[Juenapa]

hstaubyn option would great, changed it slightly to get rid of the message box.

Just need to be able to do 5 pages in stead of 1.

Thanks

This works:

Option Explicit

Sub ClearA1toJ92()

Dim ws As Worksheet
Dim TheName As String

TheName = "Sheet2"
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = TheName Then
ws.Range("A1:J9").ClearContents
End If
Next ws

End Sub

 

[Juenapa]

I now have this (See Below), which works. But I now need to do the same but instead of ClearContents I need to unlock cells.

The vba code when recording a new macro is:
Selection.Locked = False
Selection.FormulaHidden = False


Thanks

Jason


Option Explicit

Sub exam()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"
Case Else
wks.Range("A1:J9").ClearContents
End Select
Next
End Sub

 

[GTO]

Cross-posted: http://www.vbaexpress.com/forum/showthread.php?t=36443

Jason, you may wish to read: Here

 

[Juenapa]

Just change it slightly and it works fine.

Thanks

Sub AA_Unlock_Cells()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "Home", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"
Case Else
wks.Range("b2:f6").Locked = False

End Select
Next
End Sub