Hide the current month columns

I am creating a monthly forecast sheet. There are columns P to Z listed
as forecast columns with the MOnths ( Jan to Dec).
Cell A1 in the sheet is the currrent month cell which changes every
month.

I want the sheet ot hide the current month column. Suppose if A1= Jan,
then column "P( Jan ) " should hide itself. Similarly if the current
month A1= June then the columns "P to U" should hide.

I would greatly appreciate if someone cold help me with this problem.

Thanks

iCol = Application.Match(A1,Range("P1:Z1"),0)
Columns("P:P").Resize(iCol-15).Hidden = True

This assumes that A1 has the month name string, not a date,a s does P1:Z1,
and that all months are in that range.
--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> I am creating a monthly forecast sheet. There are columns P to Z listed
> as forecast columns with the MOnths ( Jan to Dec).
> Cell A1 in the sheet is the currrent month cell which changes every
> month.
>
> I want the sheet ot hide the current month column. Suppose if A1= Jan,
> then column "P( Jan ) " should hide itself. Similarly if the current
> month A1= June then the columns "P to U" should hide.
>
> I would greatly appreciate if someone cold help me with this problem.
>
> Thanks
>

Hi you can try this, the first lot of code gets pasted into the code sheet for sheet1 (assuming this is where you want to do the business) and the next lot can just go in a module of its own, it worked for me. When you activate the sheet it will hide the column that has the header matching A1 when you close the work book it will un hide all columns ready for the next time you open it, i'm sure there are better ways of achieving this but this should get you started.

Simon

Private Sub Worksheet_Activate()
Dim Rng As Range
Dim mycell
With Sheets("Sheet1")
Set Rng = Range("P1:Z1")
For Each mycell In Rng
If mycell.Text = Range("A1").Text Then
mycell.EntireColumn.Select
Selection.EntireColumn.Hidden = True
End If
Next mycell
End With
End Sub

Sub Auto_Close()
Cells.Select
Selection.EntireColumn.Hidden = False
End Sub

Thanks Simon,
I tried to use the Macro , but it hides all the columns from C to Z. I
could not understand why? Can you guide me more in this problem.

Thanks

Thanks Simon,
I tried to use the Macro , but it hides all the columns from C to Z. I
could not understand why? Can you guide me more in this problem.

Thanks

Thanks Bob,
I tried to use this but its giving me error. and not working. Is there
any other way to do achieve the results.

Thanks

Sorry, here is a correction

iCol = Application.Match(Range("A1"), Range("P1:Z1"), 0)
Columns("P:P").Resize(iCol).Hidden = True


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Thanks Bob,
> I tried to use this but its giving me error. and not working. Is there
> any other way to do achieve the results.
>
> Thanks
>

It only works from P to Z. It will have no effect on columns before P
unless you have Merged cells. Do you have merged cells in your sheet?

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Thanks Simon,
> I tried to use the Macro , but it hides all the columns from C to Z. I
> could not understand why? Can you guide me more in this problem.
>
> Thanks
>

Bob It gives me the following error

Run Time error 1004
Application defined or object defined error.


I m not good in macros.If there is something missing that i m not
adding then please let me know.

thanks again

Thanks ....Yes I had a first rwo as merged cells. Its working now.
Thanks sooooo much.