Store visible property of worksheets as variable

[yoninov]

Hi All--

I have some VBA that part of the code needs to unhide some sheets in the workbook then adds a row in the same location in every sheet. Then it copies the row above it on all those sheets. I am trying to find a way to save as a variable the visible property of those sheets before the macro unhides them so that when its done it can restore them to their original state. There are 15 sheets and each one may be hidden or unhidden when the macro is run.

I was thinking something like looping through all worksheets in the workbook and storing the hidden ones with a number (e.g. 1) then after the macro is completed it will loop through the worksheets again and all the sheets with a 1 will be hidden again.

Any thoughts?

Side note: This is the part on my code of why I need to unhide all the sheets (because I select them). If there is a work around for this, and I won't need to unhide any of the sheets to begin with that would be even better.

    Sheets(Array(Sheet4.Name, Sheet11.Name, Sheet12.Name, Sheet13.Name, Sheet14.Name, Sheet15.Name, Sheet16.Name, _
            Sheet17.Name, Sheet18.Name, Sheet19.Name, Sheet20.Name, _
            Sheet21.Name, Sheet22.Name, Sheet23.Name, Sheet37.Name, Sheet45.Name, Sheet46.Name)).Select
        
    'copies the formuals down from row above
    Rows(ActvRow).Select
    Selection.Insert Shift:=xlDown
    Rows(ActvRow).Select
    Selection.FillDown

 

[StephenCrump]

You could store the value of each worksheet's .Visible property, and set back to that value when you're finished.

But the better solution, as you thought, would be to avoid using .Select

For Each ws In Array(Sheet4, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, _
        Sheet19, Sheet20, Sheet21, Sheet22, Sheet23, Sheet37, Sheet45, Sheet46)
    With ws.Rows(ActvRow)
       .Copy
       .Insert Shift:=xlDown
    End With
Next ws