If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Clear Contents
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 |
#2
|
|||
|
|||
Clear Contents
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 |
#3
|
|||
|
|||
Clear Contents
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 |
#4
|
|||
|
|||
Clear Contents
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 |
#5
|
|||
|
|||
Clear Contents
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 |
#6
|
|||
|
|||
Clear Contents
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 |
Thread Tools | |
Display Modes | |
|
|