I need help modifying this VBA for excel 2010?

Question!

I have this piece of code here.

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 run a button on the active sheet. I want the button to do the same thing for two different sheets. Can anyone help me with this?



Answers

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

By : SLaks


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
By : aevanko


This video can help you solving your question :)
By: admin