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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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

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

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" wrote:

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.

Y
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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

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

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
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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" wrote:

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.

Y
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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

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

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




--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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" wrote:

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.

Y
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristine" wrote in message
...
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