Deleting values on various sheets

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I have a command button of which when run it will delete certain cell value on different worksheets.
The worksheets are all in the same workbook called ACCOUNTS

The below code is what ive done so far BUT im stuck with the code that fits where you see CODE GOES HERE
The part which i always get wrong where to tell the code to plly the deletion to what sheet



Rich (BB code):
Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & vbNewLine & "ON ALL WORKSHEETS" & vbNewLine & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
  Exit Sub
Else
End If

CODE TO GO HERE

End Sub

REFERENCE BELOW FOR WHICH SHEETS / CELLS NEED TO BE DELETED

INCOME (1}

Range("A4:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents

INCOME (2)

Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents

INCOME (3)

Range("A5:G28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("C4:G4").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (1)

Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A4:K28").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (2)

Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents

EXPENSES (8)
Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents

mileage (1)

Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
In that case can you please post your current code.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
Here we go.


VBA Code:
Private Sub ClearSheetValues_Click()
Dim answer As Integer
answer = MsgBox("THIS WILL CLEAR ALL CELL VALUES" & vbNewLine & vbNewLine & "ON ALL WORKSHEETS" & vbNewLine & vbNewLine & "CLICK YES TO CONTINUE", vbYesNo + vbCritical, "ACCOUNTS CLEAR VALUES MESSAGE")
If answer = vbNo Then
Exit Sub

Else
End If
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

With ThisWorkbook.Sheets("INCOME (3)")
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

With ThisWorkbook.Sheets("EXPENSES (1)")
Application.EnableEvents = False
On Error Resume Next
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("A4:K28").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (2)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (3)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (4)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (5)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (6)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES (7)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("EXPENSES( 8)")
Application.EnableEvents = False
On Error Resume Next
.Range("A5:K28").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1:C1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("D4:K4").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With

With ThisWorkbook.Sheets("MILEAGE")
Application.EnableEvents = False
On Error Resume Next
.Range("B1:D1").SpecialCells(xlCellTypeConstants).ClearContents
.Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
.Range("A34").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True
End With
    
ActiveWorkbook.Save
Application.ScreenUpdating = True
    
MsgBox "ALL WORKSHEET CELL VALUES" & vbNewLine & "" & vbNewLine & "HAVE NOW BEEN DELETED", vbInformation, "DELETED VALUES SUCCESSFUL MESSAGE"

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
You should not be getting a "No Cells were found" 1004 error with that code.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

As a test i used your code from post #7
It cleared fine but go run it again pops up the message.

Is there another way of writing that on error code
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
You have the wrong sheet name on this line of code
VBA Code:
With ThisWorkbook.Sheets("EXPENSES( 8)")
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Ive just changed that so thew gap isnt there but if that isnt what you mean im lost.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
It needs to be exactly as the name of the sheet on the tab.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,000
Office Version
  1. 2007
Platform
  1. Windows
So i now have it at this,

With ThisWorkbook.Sheets("EXPENSES (8)")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,256
Office Version
  1. 365
Platform
  1. Windows
And?
Do you still get an error?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,982
Messages
5,656,220
Members
418,290
Latest member
ArrArkRE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top