Hide Columns Based on Condition
 

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu
and the cell within the Menu worksheet I want referenced is B9. This
is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim Menu As String, rngDate As Range
Menu = Me.Name
Set rngDate = Worksheets(Menu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < Menu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1,
"IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If


End Sub


Thanks for your patience.



Tom Ogilvy wrote:
I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

I assumed you would not have dates going all the way to column IV. It worked
for me in that case. If you do have dates going all the way to column IV
change the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = LCase(Me.Name)
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
' Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
Set rng = sh.Range("A1:IV1")
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

--
Regards,
Tom Ogilvy

Hide Columns Based on Condition
 

Also, you have to edit the cell/change the date in cell B9 on sheet menu to
make it fire.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu
and the cell within the Menu worksheet I want referenced is B9. This
is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim Menu As String, rngDate As Range
Menu = Me.Name
Set rngDate = Worksheets(Menu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < Menu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1,
"IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If


End Sub


Thanks for your patience.



Tom Ogilvy wrote:
I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

So all I need to do is paste the syntax exactly as it is into each
worksheet, or do I need to change how the Menu worksheet is referenced
in the syntax. Also, which worksheets do I paste this in....the "menu"
or each "account".

Tom Ogilvy wrote:
Also, you have to edit the cell/change the date in cell B9 on sheet menu to
make it fire.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu
and the cell within the Menu worksheet I want referenced is B9. This
is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim Menu As String, rngDate As Range
Menu = Me.Name
Set rngDate = Worksheets(Menu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < Menu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1,
"IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If


End Sub


Thanks for your patience.



Tom Ogilvy wrote:
I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?

Hide Columns Based on Condition
 

Based on my understanding of what you want to do and my assumption that it
would only need to run if you change the date in B9 on the sheet menu,
Just place it in the menu sheet code module.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

So all I need to do is paste the syntax exactly as it is into each
worksheet, or do I need to change how the Menu worksheet is referenced
in the syntax. Also, which worksheets do I paste this in....the "menu"
or each "account".

Tom Ogilvy wrote:
Also, you have to edit the cell/change the date in cell B9 on sheet menu to
make it fire.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

I have tried pasting this in with no luck. I just want to make sure I
am doing everything correctly. The name of the menu worksheet is Menu
and the cell within the Menu worksheet I want referenced is B9. This
is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim Menu As String, rngDate As Range
Menu = Me.Name
Set rngDate = Worksheets(Menu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < Menu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1,
"IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If


End Sub


Thanks for your patience.



Tom Ogilvy wrote:
I see there were a couple of typos in the original, but I would see it firing
only when you change the date in the menu sheet - my interpretation of
automatically.

right click on the sheet tab of that sheet and select view code, then put in
code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, rng As Range, cell As Range
Dim sMenu As String, rngDate As Range
sMenu = Me.Name
Set rngDate = Worksheets(sMenu).Range("B9")
If Target.Address = rngDate.Address Then
For Each sh In Worksheets
If LCase(sh.Name) < sMenu Then
sh.Columns.Hidden = False
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, "IV").End(xlToLeft))
For Each cell In rng
If IsDate(cell.Value) Then
If cell.Value rngDate.Value Then
cell.EntireColumn.Hidden = True
End If
End If
Next
End If
Next
End If

End Sub

If you want something different, you would have to define automatically.

--
Regards,
Tom Ogilvy


"Skornia115" wrote:

Thanks Tom. If I want this to occur automatically, is there anything
specific I need to do with the syntax?

Tom Ogilvy wrote:
Change sMenu to hold the name of the Menu sheet and rngDate to refer to the
cell containing the date.

Sub Hidecolumns()
Dim sh as worksheet, rng as Range, cell as Range
Dim sMenu as String, rngDate as Range
sMenu = lcase("menu")
set rngDate = worksheets(sMenu").Range("B9")
for each sh in Worksheets
if lcase(sh.Name) < sMenu then
sh.Columns.Hidden = False
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,"IV").End(xltoLe ft))
for each cell in rng
if isdate(cell).Value then
if cell.Value rngDate.Value then
cell.EntireColumn.Hidden = True
end if
end if
next
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Skornia115" wrote:

New to VBA and need help.

I am trying to hide columns based on a condition. My workbook has
numerous worksheets. Each worksheet contains data for a specific
"account" and each column within the "account" worksheet has month end
data. Therefore row 1 of each worksheet contains month-end values and
each row below has various data for that specific "account."
Furthermore, the first worksheet, which basically serves as a "menu",
has a cell with a specific date. What I would like to do is write a
macro in each worksheet representing an "account" that hides all
columns in the "account" worksheets that have not occurred yet. (i.e.
If the date on the menu worksheet is 3/31/05, I would like all columns

that have month-end values exceeding 3/31/05 to be hidden). Any
suggestions?