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

Hide the current month columns
 

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)

wrote in message
oups.com...
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

Hide the current month columns
 

Hi you can try this, the first lot of code gets pasted into the cod
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 heade
matching A1 when you close the work book it will un hide all column
ready for the next time you open it, i'm sure there are better ways o
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 Su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=51751

Hide the current month columns
 

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

Hide the current month columns
 

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

Hide the current month columns
 

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

Hide the current month columns
 

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)

wrote in message
oups.com...
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

Hide the current month columns
 

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


wrote in message
oups.com...
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

Hide the current month columns
 

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

Hide the current month columns
 

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