Error when running macro on hidden sheets in Excel

November 16, 2021

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!

>Solution :

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