Hide Columns in a worksheet based on dropdown cell value in other worksheet

0

Hi,

Total beginer when it comes to coding so any help would be gretaly appreciated.

I have two worksheets, and i want to hide certain columns in one worksheet ("Results Summary"), based on the value of a dropdown cell in another worksheet ("Corporate Summary").

In essence:

If cell value in "Corporate Summary" is "N/A", columns D to H in "Results Summary" are hidden;

If value is "1", columns E to H are hidden

"2", columns F to H are hidden

"3", columns G to H  are hidden

"4", column H is hidden

"5", columns D to H are unhidden

I'm currently using the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Worksheets("Corporate Summary").Range("F14").Value Then
        Select Case Target.Value
        Case Is = "N/A"
            Columns("D").EntireColumn.Hidden = True
            Columns("E").EntireColumn.Hidden = True
            Columns("F").EntireColumn.Hidden = True
            Columns("G").EntireColumn.Hidden = True
            Columns("H").EntireColumn.Hidden = True
        Case Is = "1"
            Columns("D").EntireColumn.Hidden = False
            Columns("E").EntireColumn.Hidden = True
            Columns("F").EntireColumn.Hidden = True
            Columns("G").EntireColumn.Hidden = True
            Columns("H").EntireColumn.Hidden = True
        Case Is = "2"
            Columns("D").EntireColumn.Hidden = False
            Columns("E").EntireColumn.Hidden = False
            Columns("F").EntireColumn.Hidden = True
            Columns("G").EntireColumn.Hidden = True
            Columns("H").EntireColumn.Hidden = True
        Case Is = "3"
            Columns("D").EntireColumn.Hidden = False
            Columns("E").EntireColumn.Hidden = False
            Columns("F").EntireColumn.Hidden = False
            Columns("G").EntireColumn.Hidden = True
            Columns("H").EntireColumn.Hidden = True
        Case Is = "4"
            Columns("D").EntireColumn.Hidden = False
            Columns("E").EntireColumn.Hidden = False
            Columns("F").EntireColumn.Hidden = False
            Columns("G").EntireColumn.Hidden = False
            Columns("H").EntireColumn.Hidden = True
        Case Is = "5"
            Columns("D").EntireColumn.Hidden = False
            Columns("E").EntireColumn.Hidden = False
            Columns("F").EntireColumn.Hidden = False
            Columns("G").EntireColumn.Hidden = False
            Columns("H").EntireColumn.Hidden = False
        
        End Select
    End If
End Sub

I currently have this in the VBA editor for the "Results Summary" tab.

Obviously its not working and any steerance at all would be a massive help!

Thanks :-)

Answer
Discuss

Answers

0

You may want to try a different approach?  My thought is to  the Results Summary tab PULL the info from Corporate Summary and depending on the results, as you describe, put "HIDE" in the respective columns. Then have a macro HIDE the columns with 'Hide' in the row you put the formula in.  ( I sometimes use ShortCut key for hiding Columns - Ctrl + 0 to hide columns...so if the formula puts the word 'HIDE' in a row, I then use the short cut to do the hiding...and little vba is even required!)

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login