CODEWITHSUNDEEP
excelpassword-protectionexcel-2010vba
digitalgavakie
digitalgavakie

Reputation: 41

Run code on multiple sheets

I have this code.

Const pw   As String = "password"    '<-change password here
ActiveSheet.Unprotect pw
Range("B7").QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Protect pw

It unprotects, refreshes data and reprotects. Currently it runs from a button on the active sheet. I want the button to do the same thing for two different sheets.

Upvotes: 0

Views: 208

Answers (2)

Gaijinhunter
Gaijinhunter

Reputation: 14685

Here's an example of how you can set up an array of sheets to go through and using the With statement for cleaner code. If you want to do this for all sheets, you can simple say "For Each sheet in Worksheets" with no need to declare an array of sheets. :)

Sub Test()

Dim pw As String
pw = "password"
Dim sheet As Variant
Dim refreshSheets(1 To 2) As Worksheet

Set refreshSheets(1) = sheets(1)
Set refreshSheets(2) = sheets(2)

For Each sheet In refreshSheets
    With sheet
        .Unprotect pw
        .Range("B7").QueryTables.Refresh BackgroundQuery:=False
        .Protect pw
    End With
Next

End Sub

Upvotes: 1

SLaks
SLaks

Reputation: 888167

You're looking for ActiveWorkbook.Sheets(someName).

Upvotes: 0

Related Questions