Clear data in sheet array

[Marty Plante]

I'm struggling with some code. 5 sheets in a workbook, 4 require all unprotected cells have data deleted to reset the workbook to original state.

I've selected the 4 sheets with a sheet array:
Sheets(Array("BOP_Commission_Pay", "Pumper", "Expense_Report", _
"Shop Travel Mobe-Demobe Time")).Select

Code I need to run: (keep in mind this may not be the most elegant code, I'm not qualified to judge)
Public Sub ClearUnlockedCells()
Dim UnlockedCells As range
Dim Cell As range

On Error GoTo ErrHandler
If Not TypeOf ActiveSheet Is Worksheet Then
MsgBox "You must activate a worksheet first.", vbExclamation
GoTo ExitProc
End If

For Each Cell In ActiveSheet.UsedRange
If Not Cell.Locked Then
If UnlockedCells Is Nothing Then
Set UnlockedCells = Cell
Else
Set UnlockedCells = Union(UnlockedCells, Cell)
End If
End If
Next Cell

If UnlockedCells Is Nothing Then
MsgBox "No unlocked cells were found.", vbExclamation
Else
UnlockedCells.ClearContents
End If

ExitProc:
On Error Resume Next
Set UnlockedCells = Nothing
Set Cell = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical
Resume ExitProc

End Sub

My problem:
I expected this to clear all data in unprotected cells on all 4 sheets of the array. Only the active sheet gets cleared, not all the selected sheets.
Can the data not be cleared from a selected sheets or do I need to loop through each?

I know vba is the solution, just not able to put the pieces all together.

Thankful for any help I can get.

 

[HaHoBe]

Hi Marty Plante,

For Each Cell In ActiveSheet.UsedRange

should only work on the ActiveSheet not on those selected.

Please try

Public Sub ClearUnlockedCells()
'https://www.mrexcel.com/board/threads/clear-data-in-sheet-array.1228150/
Dim UnlockedCells As Range
Dim Cell As Range
Dim ws As Worksheet

On Error GoTo ErrHandler
If Not TypeOf ActiveSheet Is Worksheet Then
  MsgBox "You must activate a worksheet first.", vbExclamation
  GoTo ExitProc
End If

For Each ws In Sheets(Array("BOP_Commission_Pay", "Pumper", "Expense_Report", _
                            "Shop Travel Mobe-Demobe Time"))
  For Each Cell In ws.UsedRange
    If Not Cell.Locked Then
      If UnlockedCells Is Nothing Then
        Set UnlockedCells = Cell
      Else
        Set UnlockedCells = Union(UnlockedCells, Cell)
      End If
    End If
  Next Cell
  
  If UnlockedCells Is Nothing Then
    MsgBox "No unlocked cells were found on " & ws.Name, vbExclamation
  Else
    UnlockedCells.ClearContents
  End If
  Set UnlockedCells = Nothing
Next ws

ExitProc:
On Error Resume Next
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical
Resume ExitProc

End Sub

Ciao,
Holger

 

[Marty Plante]

Hi Marty Plante,

For Each Cell In ActiveSheet.UsedRange

should only work on the ActiveSheet not on those selected.

Please try

Public Sub ClearUnlockedCells()
'https://www.mrexcel.com/board/threads/clear-data-in-sheet-array.1228150/
Dim UnlockedCells As Range
Dim Cell As Range
Dim ws As Worksheet

On Error GoTo ErrHandler
If Not TypeOf ActiveSheet Is Worksheet Then
  MsgBox "You must activate a worksheet first.", vbExclamation
  GoTo ExitProc
End If

For Each ws In Sheets(Array("BOP_Commission_Pay", "Pumper", "Expense_Report", _
                            "Shop Travel Mobe-Demobe Time"))
  For Each Cell In ws.UsedRange
    If Not Cell.Locked Then
      If UnlockedCells Is Nothing Then
        Set UnlockedCells = Cell
      Else
        Set UnlockedCells = Union(UnlockedCells, Cell)
      End If
    End If
  Next Cell
 
  If UnlockedCells Is Nothing Then
    MsgBox "No unlocked cells were found on " & ws.Name, vbExclamation
  Else
    UnlockedCells.ClearContents
  End If
  Set UnlockedCells = Nothing
Next ws

ExitProc:
On Error Resume Next
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical
Resume ExitProc

End Sub

Ciao,
Holger

Worked exactly as I need it to. Thank you for this.