VBA code to hide tabs calculated cell value

[locker38]

I went to a Mr. Excel workshop a few months ago and told me that this board was a great resource. I know excel fairly well however my VBA skills are lacking. I did try to search the site and I found similar or related posts but I couldn’t find exactly what I needed. Any help is greatly appreciated.

I want to run VBA code a single time to hide all tabs that have “HIDE” in cell G1. I am creating a master budget worksheet that will be used for approximately 30 departments. The worksheet has a summary tab, a master data tab and then a tab for each GL account that is used (approximately 200 tabs). I have a drop down list on the first page to select department and then all 200 tabs are updated with formulas that pull from the master data tab. Each department only uses some fraction of the possible GL accounts. I spent countless time last year hiding unused tabs for each department.

Here is a summary of the steps that I would take for each department:

• Open master template/file
• Select department on summary tab
• I will put a formula in cell G1 on each to the 200 GL tabs that will equal hide if that department has no prior budget or actuals for that account
• Run VBA to hide all tabs with the phrase “HIDE” in cell G1
• Save and email worksheet to department head
• Repeat for the next department

 

[My Aswer Is This]

This script will hide all sheets with "HIDE" in Range("G1")
Not sure about the other things you want and what part if any of this have you already done.

Sub Hide_Sheets()
'Modified 3-7-18 7:00 PM EST
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
     If Sheets(i).Range("G1").Value = "HIDE" Then Sheets(i).Visible = False
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If you want a script to enter a formula into a certain range in all sheets then try this:

Sub Insert_Formula()
'Modified 3-7-18 7:20 PM EST
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
        Sheets(i).Range("G1").Formula = "=B1+B2"
     Next
Application.ScreenUpdating = True
End Sub

 

[locker38]

Awesome. Thank you very much.

 

[My Aswer Is This]

Awesome. Thank you very much.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.