Macro/VBA to program automatic grouping of columns

[matp2530]

Hi Everyone, I am very limited in my Macro/VBA skills and have been trying to shorten a process my team and I do multiple times a month. Every reporting period we have to go through our reports and group/ungroup columns based on what period it is. For example, we may have the columns set up as:

W1 W2 W3 W4 WT G1 G2 G3 G4 GT

Once we get to period 3 we want to only show:

W3 WT G3 GT

Is there a way I could set up an automatic update that would group/ungroup the correct columns and preferably be able to do it based on what period we are in.

The biggest issue we have run into is each report has the same column labelings, but they might not all start in the same column. For example, some people start W1 in Column C compared to others who start it in Column S.

I apologize for the long-winded question, but feel free to ask any additional questions you need. Also, feel free to tell me I'm crazy and none of this made sense, I will be more than willing to try and re-word or fix any of it..

THANK YOU!

 

[pbornemeier]

If the second character of the column headers refer to the period (and total) you want to show then this code should work:

Option Explicit

Sub ShowSelectedColumns()

    Dim lLastColumn As Long
    Dim lX As Long
    Dim sPeriodToShow As String
    
    lLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    
    sPeriodToShow = InputBox("Enter the period to show,  'X' to unhide all columns.", "Enter Period", "X")
    
    If UCase(sPeriodToShow) = "X" Then
        Cells.EntireColumn.Hidden = False
    Else
        For lX = 1 To lLastColumn
            Select Case UCase(Right(Cells(1, lX).Value, 1))
            Case "T", UCase(sPeriodToShow)
                'do nothing
            Case Else
                Columns(lX).EntireColumn.Hidden = True
            End Select
        Next
    End If
    
End Sub