VBA - If sheet is visible, perform task, If not visible, perform different task

[delaney1102]

Hi all, I have created a macro (that I largely copied from an already existing one in the same workbook, I just needed some slightly different things done) in a workbook with about 15 tabs that takes data from an xl hidden tab and copies it over to a master file in another location, that all works fine. The issue I'm coming across is at the end of the code, I need to type some text in a specific cell on whichever of 2 tabs are currently visible. If Worksheet A is visible, then I want the text in J37 but if Worksheet A is hidden, then I can skip that and move to Worksheet B and type the text in J23. I've played around with some IFs and visible=true, but still coming across some errors. Any help is appreciated!


Application.ScreenUpdating = True
Worksheets("A").Select
Range("J37").Value = "TEXT " & Now()

Worksheets("B").Select
Range("J23").Value = "TEXT " & Now()

Worksheets("A").Protect "PASSWORD"
Worksheets("B").Protect "PASSWORD"

Application.CutCopyMode = False

Sheets("Z").Select
Range("A1").Select

MsgBox "JOB COMPLETE."

Exit Sub

 

[dmt32]

Hi,
see if following will do what you want

   With Worksheets("A")
        If .Visible Then .Range("J37").Value = "TEXT " & Now()
    End With
    
    With Worksheets("B")
        If .Visible Then .Range("J23").Value = "TEXT " & Now()
    End With

or do it all in a loop maybe

    Dim i As Long
    For i = 1 To 2
     With Worksheets(Chr(i + 64))
        .Unprotect "PASSWORD"
        If .Visible Then .Range(Choose(i, "J37", "J23")).Value = "TEXT " & Now()
        .Protect "PASSWORD"
     End With
    Next i

Dave

 

[JoeMo]

Assuming that if A is hidden B will be visible, maybe:

Application.ScreenUpdating = False
With Worksheets("A")
    If .Visible = xlHidden Then
        With Worksheets("B")
            .Range("J23").Value = "TEXT " & Now()
            .Protect "PASSWORD"
        End With
    Else
        .Range("J37").Value = "TEXT " & Now()
        .Protect "PASSWORD"
    End If
End With
Sheets("Z").Select
Range("A1").Select
MsgBox "JOB COMPLETE."
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With

 

[delaney1102]

Hi,
see if following will do what you want

   With Worksheets("A")
        If .Visible Then .Range("J37").Value = "TEXT " & Now()
    End With
   
    With Worksheets("B")
        If .Visible Then .Range("J23").Value = "TEXT " & Now()
    End With

or do it all in a loop maybe

    Dim i As Long
    For i = 1 To 2
     With Worksheets(Chr(i + 64))
        .Unprotect "PASSWORD"
        If .Visible Then .Range(Choose(i, "J37", "J23")).Value = "TEXT " & Now()
        .Protect "PASSWORD"
     End With
    Next i

Dave

that first one worked!! you're a rockstar, thank you!! i'm sure the others would have too, but i got it to work once, i'm moving on hahaha

 

[dmt32]

You are welcome glad we were able to help & appreciate the feedback

Dave