CODEWITHSUNDEEP
excelvba
Gray Meiring
Gray Meiring

Reputation: 421

How to modify hidden sheets?

I am running the following macro to copy down formulas on two hidden sheets.

With the sheets unhidden the code (excluding the later added .visible syntax below) worked. However, not when I hide the sheets.

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

Upvotes: 1

Views: 265

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

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

Upvotes: 2

Related Questions