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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,373
Members
410,679
Latest member
rolandbianco
Top