CODEWITHSUNDEEP
excelvba
MooChang Park
MooChang Park

Reputation: 81

I want to set multiple sheets as the range for macro

I want to set multiple sheets(sheet1 , sheet2) at the start of the macro and at the moment it is not working.

Private Sub Workbook_Open()
'Dim ws As Worksheet: Set ws = Sheets("sheet1","sheet2")
    If Range("W6").Value = 0 Then
        Call HideFG
    Else
        Call HideF
    End If
End Sub

Upvotes: 0

Views: 986

Answers (2)

CLR
CLR

Reputation: 12279

you appear to be trying to gather multiple sheets into one reference:

Set ws = Sheets("sheet1","sheet2")

You can almost do this with:

Sheets(Array("sheet1","sheet2"))

However, you have to work on one sheet at a time.. so you need to use it like so:

For Each ws In Sheets(Array("sheet1", "sheet2"))

    If ws.Range("W6").Value = 0 Then
        Call HideFG
    Else
        Call HideF
    End If

Next

Upvotes: 5

Teamothy
Teamothy

Reputation: 2016

Am I guessing right what You mean?

Private Sub Workbook_Open()

Dim i As Long
    For i = 1 To ThisWorkbook.Sheets.Count
        With ThisWorkbook.Sheets(i)
            If .Range("W6").Value = 0 Then
                Call HideFG
            Else
                Call HideF
            End If
        End With
    Next

End Sub

Upvotes: 1

Related Questions