ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Hi,
In my workbook i have 12 worksheets.
Not all worksheets are used every time due to sales etc.
I use the code below to clear the values from the cells within there ranges on each sheet BUT i wish to leave the formulas intact.
I have noticed that when i run the code various formulas are deleted & i manually need to put them back.
Do you see a reason why this happens as it defeats the object.
The code shown below is the same for each page so here is a partial piece of it for you.
So here is an example.
Worksheet INCOME 1
Code is run & then looking at the sheet the fomulas in the cells below are deleted.
D4:D8 D9 onwards are all intact
F4:F7 F8 onwards are all intact
In my workbook i have 12 worksheets.
Not all worksheets are used every time due to sales etc.
I use the code below to clear the values from the cells within there ranges on each sheet BUT i wish to leave the formulas intact.
I have noticed that when i run the code various formulas are deleted & i manually need to put them back.
Do you see a reason why this happens as it defeats the object.
The code shown below is the same for each page so here is a partial piece of it for you.
Rich (BB code):
With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
On Error Resume Next
.Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With
With ThisWorkbook.Sheets("INCOME (2)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With
So here is an example.
Worksheet INCOME 1
Code is run & then looking at the sheet the fomulas in the cells below are deleted.
D4:D8 D9 onwards are all intact
F4:F7 F8 onwards are all intact