PDA

View Full Version : Macro to UnGroup and Group Based on Current Month



RogChele
05-10-2017, 01:37 PM
Good Afternoon,
19126

I am searching for a macro that allows me to ungroup columns of months (April - December), leave ungrouped the previous month (which in this example is April - reporting is done based on prior month) and then re-group (May - December).

I have tried recording the macro from excel but I am unsure how to add an IF / THEN STATEMENT in the sense that the macro would recognize the previous month from current month and leave that column open to input data.

Currently cell A1 is used to calculate YTD (sum/offset) but maybe it can also be used in the macro to figure out which month I would like to leave ungrouped.

There are three (3) tabs in the workbook. Is there a way that the macro can change on all sheets based off the current month on the first sheet? OR would I need to create a different macro for each worksheet?

All assistance is appreciated.

Thank you.

Bob Phillips
05-11-2017, 03:18 AM
Public Sub ResetGroups()
Dim ws As Worksheet
Dim idxMonth As Long

idxMonth = Worksheets(1).Range("A1").Value

For Each ws In ActiveWorkbook.Worksheets

ws.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
If ws.Columns(idxMonth + 1).OutlineLevel = 2 Then ws.Columns(2).Resize(, idxMonth).Ungroup
Next ws
End Sub