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

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" skrev i meddelelsen
...
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

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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

Try changing:

cell.ClearContents
to
cell.value = ""



gibbylinks wrote:

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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



--

Dave Peterson

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" wrote:

Try changing:

cell.ClearContents
to
cell.value = ""



gibbylinks wrote:

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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



--

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.


gibbylinks wrote:

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" wrote:

Try changing:

cell.ClearContents
to
cell.value = ""



gibbylinks wrote:

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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



--

Dave Peterson


--

Dave Peterson