Deleting values on various sheets

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Yes
It keeps showing the message for the first sheet

Did you use my code i posted to see if it worked for you,also uploaded a file of my workbook
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
What message?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
See attached screenshots please.
 

Attachments

  • 192.jpg
    192.jpg
    30.1 KB · Views: 3
  • 193.jpg
    193.jpg
    37.5 KB · Views: 3
  • 194.jpg
    194.jpg
    157.3 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
In that case there is something different between the workbook your using & the one you uploaded.
You should not get that error.
 

ipbr21054

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

ADVERTISEMENT

The workbook i upload is that of this one.

Did you try the one i uploaded ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Yes I did & it works fine, once the sheet name was corrected.
 

ipbr21054

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

ADVERTISEMENT

Another test.
Please look at the before & after screen shots
I edited the range on the sheet INVOICE 1 like so A4:G28 to A4 and also the second line B1:C! to B1

For some reason it deletes cell value A1 and also cell vales of Row 3 ?????????
 

Attachments

  • 195.jpg
    195.jpg
    61.6 KB · Views: 1
  • 196.jpg
    196.jpg
    60.3 KB · Views: 1

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Post the actual code that you amended
A4:G28 to A4
B1:C1 to B1

I only altered this section.

Rich (BB code):
With ThisWorkbook.Sheets("INCOME (1)")
Application.EnableEvents = False
.Range("A4").SpecialCells(xlCellTypeConstants).ClearContents
.Range("B1").SpecialCells(xlCellTypeConstants).ClearContents
Application.EnableEvents = True
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,129,686
Messages
5,637,827
Members
416,984
Latest member
dee10

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