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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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...
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Smitty

I see what you mean... Thanks for all your help once again.

Andrew
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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
 

Forum statistics

Threads
1,141,934
Messages
5,709,413
Members
421,635
Latest member
mehdi hannechi

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