Error when running macro on hidden sheets in Excel-CodePudding

I am running the following macro to copy down formulas on two hidden sheets. Initially I was testing with the sheets unhidden, and the code (excluding the later added .visable syntax below) worked perfectly. However, when I hide the sheets, the macro did not work.

I have tried a few bits of code to get this right, without any luck. I'm not great at VBA, and I am sure it is something simple I am missing.

Essentially, the two sheets are always hidden, and I need the script to still run. My code (with the not functioning unhide then hide attempt):

Sub TestMacro()


' Whse Tab

Sheets("Whse").Visable = True
Sheets("Whse").Select

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("K2") = "=IF(A2=1,J2,J2 K1)"
Range("K2:K" & LastRow).FillDown

Range("L2") = "=H2-K2"
Range("L2:L" & LastRow).FillDown

Range("M2") = "=IF(L2>0,J2,J2 L2)"
Range("M2:M" & LastRow).FillDown

Range("N2") = "=IF(M2>0,1,2)"
Range("N2:N" & LastRow).FillDown

Sheets("Whse").Visable = False


' AllWhse Tab

 Sheets("AllWhse").Visable = True
 Sheets("AllWhse").Select

 LastRow = Range("A" & Rows.Count).End(xlUp).Row

 Range("J2") = "=IF(A2=1,I2,I2 J1)"
 Range("J2:J" & LastRow).FillDown

 Range("K2") = "=G2-J2"
 Range("K2:K" & LastRow).FillDown

 Range("L2") = "=IF(K2>0,I2,I2 K2)"
 Range("L2:L" & LastRow).FillDown

 Range("M2") = "=IF(L2>0,1,2)"
 Range("M2:M" & LastRow).FillDown

 Worksheets("AllWhse").Visable = False

' Refresh Workbook

 ActiveWorkbook.RefreshAll


 End Sub

I just need assistance in editing the above to allow the main code to run if the sheets are hidden.

Thank you!

CodePudding user response：

Running Macro on Hidden Sheets

It is true that you can't select a hidden (not Visible) sheet, but that doesn't mean you can't modify it.

Option Explicit

Sub TestMacro()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim LastRow As Long
    
    With wb.Worksheets("Whse")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("K2:K" & LastRow).Formula = "=IF(A2=1,J2,J2 K1)"
        .Range("L2:L" & LastRow).Formula = "=H2-K2"
        .Range("M2:M" & LastRow).Formula = "=IF(L2>0,J2,J2 L2)"
        .Range("N2:N" & LastRow).Formula = "=IF(M2>0,1,2)"
    End With

    With wb.Worksheets("AllWhse")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("J2:J" & LastRow).Formula = "=IF(A2=1,I2,I2 J1)"
        .Range("K2:K" & LastRow).Formula = "=G2-J2"
        .Range("L2:L" & LastRow).Formula = "=IF(K2>0,I2,I2 K2)"
        .Range("M2:M" & LastRow).Formula = "=IF(L2>0,1,2)"
    End With
 
End Sub