CODEWITHSUNDEEP
excelvba
jonathan marin
jonathan marin

Reputation: 9

Looping through Inactive worksheets

Loop code doesnt not on all worksheets at once unless i go to each tab and run it

Dim Sheets As Variant
Dim Sheet As Variant

Sheets = Array("1", "2", "3", "4", "5", "6")

For Each Sheet In ActiveWorkbook.Sheets

If Range("G2").Value = 1 Then Range("h10:h11").Value = 
Range("N10:N11").Value
If Range("G2").Value = 1 Then Range("h14:h22").Value = 
Range("N14:N22").Value
If Range("G2").Value = 1 Then Range("h27:h29").Value = 
Range("N27:N29").Value
Next Sheet

how can i fix my code so when im on tab 1 it changes all of the other tabs

Upvotes: 0

Views: 222

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

When you do not specify the Parent Sheet in the code you will only get the active sheet. Use a With Block:

Dim Shts As variant
Dim Sheet As Worksheet

Shts = Array("1", "2", "3", "4", "5", "6")

For Each Sheet In ActiveWorkbook.WorkSheets
    With Sheet
        If .Range("G2").Value = 1 Then 
            .Range("h10:h11").Value = .Range("N10:N11").Value
            .Range("h14:h22").Value = .Range("N14:N22").Value
            .Range("h27:h29").Value = .Range("N27:N29").Value
        End If
    End With
Next Sheet

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

Something like so, also checks sheet exists and gives the option to skip the activesheet

Sub n()

Dim Shts() As Variant
Dim ws As Worksheet
Dim s As Variant

Shts = Array("1", "2", "3", "4", "5", "6")

For Each s In Shts

    If WORKSHEET_EXISTS(CStr(s)) Then

        '   Exclude the activesheet?
        If s <> ActiveSheet.Name Then

            Set ws = ThisWorkbook.Worksheets(s)
            With ws
                If .Range("G2").Value = 1 Then
                    .Range("h10:h11").Value = .Range("N10:N11").Value
                    .Range("h14:h22").Value = .Range("N14:N22").Value
                    .Range("h27:h29").Value = .Range("N27:N29").Value
                End If
            End With

        '   End of exclude
        End If

    Else

        Debug.Print "Can't find worksheet : " & s

    End If

Next s

Erase Shts

End Sub

Function WORKSHEET_EXISTS(strWorksheetname As String) As Boolean

Dim w As Excel.Worksheet

WORKSHEET_EXISTS = False

On Error GoTo eHandle

Set w = ThisWorkbook.Worksheets(strWorksheetname)

WORKSHEET_EXISTS = True

ExitFunction:

Exit Function

eHandle:

End Function

Upvotes: 0

Related Questions