ClearContents of a range of cells in on sheets bar 5

[Juenapa]

This works:
Hi, I have made a bit of code to ClearContents of a range of cells. At the moment in clears every sheet expect one of my choosing. But I need to choose 5 sheets instead of one.

Can any one help.

Thanks.

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

[GTO]

I think you meant except one of my choosing, and now would like to skip 5 specific sheets. You can add "And Not" tests to your If, but you might want to try Select Case.

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

Hope that helps,

Mark

[Juenapa]

That worked great, thanks.

Is there a way of adapting it to create a macro to do another task for a range on all sheets except a few again?

Unlocking cell protection: VBA code:

Selection.Locked = False
Selection.FormulaHidden = False

Thanks a lot.

Jason

[GTO]

Hi Jason,

I am afraid I am not understanding the question.

Mark

[Juenapa]

I'm dealing protected sheets and locked cells. I have a macro to protect and unprotect the sheets. I need a macro to unlock cells on all sheets in a range like before. To unlock a cell, - right click a cell, format cells, protection tab, untick "locked".

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


Thanks

Jason

[Rob342]

Hi Mark / Jason

Another way if you want


Option Explicit
Private Sub ClearSheets()
Dim ws As Worksheet
Dim TheName As String
TheName = "Sheet2"

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

End Sub



I use this method to unlock sheets

Private Sub PassWord()
Dim jason As String
Dim ws As Worksheets
Dim Inpass As String

Const PWD As String = "jason"
Inpass = Application.InputBox("Please Enter The Password To Proceed. Enter To Cancel.")
If Inpass = "" Or Inpass <> PWD Then
Exit Sub
End If

'Call another routine or input code to reset locked cells
End Sub

[Juenapa]

I use this to change between un/protected sheets, but I also have a security macro that automatically locks cells after data is entered. I need a way of just unlocking a range of cells on nearly every sheet, unlocking cells not protecting sheet.

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

[GTO]

Maybe...

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.Unprotect "Your Password"
wks.Range("A1:B20").Locked = False
wks.Protect "Your Password"
End Select
Next
End Sub

[GTO]

Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=533549

Jason, you may wish to read: Here (http://www.excelguru.ca/node/7)

[Juenapa]

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