Clear contents from 52 sheets password protected??

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Hi All

I have 52 worksheets which would be password protected "LIVERPOOL" and I would like to clear the contents including formats of all the 52 sheets.

I have this so far:

Code:
Sub ClearAll()

    Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10")).Select_
    Sheets(Array("11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select_
    Sheets(Array("21", "22", "23", "24", "25", "26", "27", "28", "29", "30")).Select_
    Sheets(Array("31", "32", "33", "34", "35", "36", "37", "38", "39", "40")).Select_
    Sheets(Array("41", "42", "43", "44", "45", "46", "47", "48", "49", "50")).Select_
    Sheets(Array("51", "52")).Select
    Cells.Select
    Selection.Clear
End Sub

Anybody got any hints or tips how I could add to this?

Thanks in advance

Andrew
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Andrew

Try this.
Code:
Sub Unprotect52()
Dim ws As Worksheet

    For I = 1 To 52
        Set ws = Worksheets(CStr(I))
        With ws
            .Unprotect "LIVERPOOL"
            .Cells.ClearContents
            .Protect "LIVERPOOL"
        End With
    Next I

End Sub

EDIT: Fixed Code tags - Smitty
 
Upvote 0
Something like this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> ClearAll()
  <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
        <SPAN style="color:#00007F">With</SPAN> ws
            .Unprotect "LIVERPOOL"
            .Cells.ClearContents
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Or do you have more than 52 sheets and need to exclude some?

HTH,

Smitty
 
Upvote 0
Norie Spot on

PennySaver, I tried your code but this cleared the whole of the workbook, including my template.

How could I also make sure the tab colour have no colour, because at present they are red?

[edit] I forgot to say big thankyou...
 
Upvote 0
PennySaver, I tried your code but this cleared the whole of the workbook, including my template.

How could I also make sure the tab colour have no colour, because at present they are red?

Norie's code is more concise, but with mine you can add an exclusion:

If wsName <> "Template" Then...

As for the other question, the code for No Tab Color is:

.Tab.ColorIndex = -4142

Smitty
 
Upvote 0
Another quick question...

I have the following code which meets my requirments for individual spreadsheets....

Code:
Sub Unprotect52()
Dim ws As Worksheet

    For I = 1 To 52
        Set ws = Worksheets(CStr(I))
        With ws
            .Unprotect "liverpool"
            .Cells.ClearContents
            .Protect "liverpool"
            .Tab.ColorIndex = -4142
        End With
    Next I

End Sub

I have 75 spreadsheets which are the same, in the same directory/folder. Is there any way I could run this code and this would run on all 75 spreadsheets?

Note, 75 spreadsheets have passwords on them, but I have a full list of team names and passwords.

Any help would be much appreciated.

Thanks

Andrew
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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
Back
Top