Makro from value in one cell, hiding columns in another sheet

[Kristine]

Hi,

I do not have much experience in macro programming, but now I would need to
make a macro. Can someone please help me??

In one excel worksheet called "Menu" I have put a lookup function in cell
(A5). This function presents values 1 to 12 (and cell B5 has a similar lookup
function presenting month January to December), depending on value chosen in
a drop down list. If value in cell A5 is 1 (January in cell B5), I would like
to hide some columns in another worksheet called "Output", say columns U to
Z. I think that for hiding the columns I can use:

Columns("U:Z").Select
Selection.EntireColumn.Hidden = True

But I don't know how to start this macro, I don't know how to do the
selection of columns depending on the value in cell A5(can I use an IF
function?), and how to select the worksheet "Output"?? Can someone please
help?

Thanks a lot,
Kristine

 

[Don Guillett]

This should get you going. As written it will hide from the column NUMBER
entered in cell a5 on menu.
so,if a 5 was entered, it would hide cols e-z. NO selections. Can be fired
from anywhere in the workbook.

Sub hidecolumnsif()
mv = Sheets("menu").Range("a5")
With Sheets("output")
.Columns.Hidden = False
.Range(Cells(1, mv), Cells(1, "z")) _
.EntireColumn.Hidden = True
End With
End Sub

 

[Kristine]

Thanks a lot for your feedback.

Unfortunately I was a bit inaccurate when it comes to which columns should
be hidden.. Cell A5 will show the value for a month (1-12), and for each
month I would like to hide different columns (Value 1 in cell A5 indicates
month January, and that columns U to Z should be hidden). Thus the number in
cell A5 will not say from which column I should start hiding.. If A5 is 2
(february) I would like to hide column AA to AE etc. For each month I can
write the specific columns to be hidden{Columns("U:Z").Select
Selection.EntireColumn.Hidden = True}, I just don't know how to indicate that
the value in A5 should decide which columns to be hidden, i.e. how to write a
macro for: IF worksheet "Menu" cell A5=1, worksheet "output"
Columns("U:Z").Select Selection.EntireColumn.Hidden = True

You know how to handle this?
Thanks!

Kristine

 

[Don Guillett]

No mind readers here. You need to give a LOT more info or send your workbook
to my address below along with a very clear and detailed description of your
desires. Also copy/paste these emails to a sheet in the wb.

 

[Don Guillett]

I sent this
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("b5:c17")) Is Nothing Then Exit Sub
mv = Cells(Target.Row, "c")

Select Case LCase(mv) 'type months in lowercase
Case Is = "january": c = "u1:av1"
Case Is = "february": c = "X1:AV1"
Case Is = "march": c = "L1:N1,aa1:av1"
Case Is = "april": c = "L1:q1,ad1:av1"
Case Is = "may": c = "L1:T1,AG1:AV1"
Case Is = "june": c = "L1:w1,Aj1:AV1"
Case Is = "july": c = "L1:z1,Am1:AV1"
Case Is = "august": c = "L1:ac1,Ap1:AV1"
Case Is = "september": c = "L1:af1,As1:AV1"
Case Is = "october": c = "L1:aI1"
Case Is = "november": c = "L1:aO1"
Case Is = "december": c = "L1:aR1"
Case Else
End Select
With Sheets("output")
..Columns.Hidden = False
..Range(c).EntireColumn.Hidden = True
..Range("a2") = Target
..Select
End With
End Sub