Group column based on today's date

[TG2812]

Hi All,

I'm currently trying to come up with a VBA module that aims at grouping all columns whose date are past (against today's date). Date to check against are located in row 2 of Sheet1.

I would like the macro to execute upon the workbook opening only.

This is what I did so far but VBA returns a "run mismatch error". Any idea what i did wrong?

Private Sub Workbook_Open(ByVal Target As Range)

Dim xCell As Range
If Target.Address <> Range("A1").Address Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In Rows(2)
xCell.EntireColumn.Group = (xCell.Value < Target.Value)
Next
Application.ScreenUpdating = True
End Sub





Bear with my question, I'm a beginner in VBA world.

Thank you very much in advance for your time and answers.

 

[Fluff]

How about

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   Set Ws = Sheets("[COLOR=#ff0000]Master[/COLOR]")
   For Each Cl In Ws.Range("[COLOR=#ff0000]B1:V1[/COLOR]")
      If Cl.Value < Date Then Cl.EntireColumn.Group
   Next Cl
End Sub

Change values in red to suit

 

[TG2812]

Hi,

Your solution works great. However, each time I open the workbook, Excel creates another grouping on top of the one already created.
Do I have to ungroup upon workbook closure or is there any other way to work this around?

Many thanks in advance for your help.

 

[Fluff]

How about

Private Sub Workbook_Open()
   Dim Ws As Worksheet
   Dim Cl As Range

   Set Ws = Sheets("Master")
   Cells.ClearOutline
   For Each Cl In Ws.Range("B1:V1")
      If Cl.Value < Date Then Cl.EntireColumn.Group
   Next Cl
End Sub

 

[TG2812]

Works like a charm sir!
Thanks for your help here.

 

[Fluff]

Glad to help & thanks for the feedback