Clear Contents

[gibbylinks]

I have several sheets in one workbook I use for generating invoices. Is there
a quick way to clear them (they have different setups) leaving the formatting
and formulas intact ?

They are all protected with most cells locked. I am hoping to be able to
have one macro linked to a cell that will work with all the sheets.

thanks

Paul

 

[Per Jessen]

Hi Paul

This macro clear the contents of all unlocked cells in all sheets in the
active workbook.

You can insert a button on a sheet which can call this macro.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell as Range
For Each sh In ThisWorkbook.Sheets
For Each cell In sh.UsedRange
If cell.Locked = False Then cell.ClearContents
Next
Next
End Sub

Regards,
Per

 

[gibbylinks]

Thanks Per...fly in the ointment I have some merged cells and it stops at them

 

[Dave Peterson]

Try changing:

cell.ClearContents
to
cell.value = ""

 

[gibbylinks]

Thanks Dave that worked.....but too well.

Two questions.

1) Is there a way to restrict this to the current worksheet only ?
2) Is there a way to define the range cleared ?
(I have hyperlinks and notes on the RHS that I'd like left intact)

Sorry to keep moving the "goalposts"

Paul

 

[Dave Peterson]

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell as Range

set sh = activesheet
For Each cell In sh.Range("a1:B99").cells
If cell.Locked = False Then
cell.value = ""
end if
Next cell

End Sub

Change the address to what you need.