Macro to delete content from same range on multiple worksheets

[J4G036]

I have a spreadsheet with 13 sheets worth of information relating to a transport company's employee KPIs. It is all more or less finished the only thing I need to work out is how to set up a button to delete the content in range B3:AZ159 across all 13 worksheets.

This button should only be pressed after all the data has been collated so I have been asked if there is a way to implement a password input to stop it being used by mistake. Or at the very least one or two "are you sure" style alerts before the script is triggered.

Thanks in advance.

[TMS]

You could use a MsgBox to ask for confirmation or you can use an InputBox to request a password. Of course, if you want a password to be input, you'll need to store it somewhere (in the code or on a worksheet) so you can check it.

[MrGes]

Hi,
try this

Please Login or Register  to view this content.

[J4G036]

Thanks MrGes.

Where would you put that code exactly? In each worksheet it refers to? Or do I need to specify which worksheets within the code somewhere?

[J4G036]

That sounds like a good idea TMS. Being new to VBA though I wouldn't have a clue where to begin with writing that code though

Do you have a starting point for me to work on?

Thanks for your reply.

[TMS]

MrGes's code asks once to confirm that you want to clear the cells. If you say "yes", it loops through all the sheets and clears the range. Apply that code to your button, or call it from your button code as a free standing subroutine.

[Rick Rothstein]

Hi,
try this

Please Login or Register  to view this content.

You can do your macro without using a loop...

Please Login or Register  to view this content.

[J4G036]

Thanks Rick.

So there are a few sheets where I do not want the content to be cleared such as a reporting page. Is there a way to specify to clear content (and comments) from all worksheets with the word "week" in the name?

[TMS]

You would have to use the loop for that and check the sheet name.

[J4G036]

So I am still having trouble with this.

I have tried the following code to specify any worksheet beginning with "Week" to clear content and comments in range B3:AZ152 and have assigned it to a button on my reporting page (which I want to be disregarded by the script) which when pressed ONLY clears the content on my reporting page and not the "Week" sheets as expected. Any Ideas? I haven't yet managed to add an alert to ask if you are sure you want to do this as I want to make sure it does what it is supposed to. Thanks

Sub ClearAll()

Dim ws As Worksheet
Set Rng = Range("B3:AZ152")

For Each ws In ActiveWorkbook.Sheets

If ws.Name Like "Week *" Then
Rng.Select
Selection.ClearContents
Selection.ClearComments
Else: If Not ws.Name Like "Week *" Then Exit Sub

End If

Next ws


End Sub

[J4G036]

Okay I think I am making progress. I am now using the following code.

Sub ClearAll()

Dim ws As Worksheet

mymsg = MsgBox("Are you sure? This content cannot be recovered once cleared.", vbYesNo)
If mymsg = vbYes Then
For Each ws In Worksheets

If ws.Name Like "Week *" Then
ws.Range("B3:AZ159").ClearContents
ws.Range("B3:AZ159").ClearComments


End If


Next ws

End If

End Sub



The problem now is that the "Week" sheets are protected so as to avoid any inadvertent deletions of other unrelated formulas. I know there is a way to bypass this but I am not sure how. The sheets have all been set to allow objects to be edited but what needs putting into the code?

Thanks

[J4G036]

Fixed it. Certain cells were still locked. Thanks for your help all.

[TMS]

You're welcome.