VBA to find current month and year - Hide all others

[CLAZGRE]

Good Afternoon All,

I am looking to hide all columns within a range of columns that are not the current month. It worked perfectly when I didn't have my sheet formatted as a table, however it is easier to use my sheet for other purposes formatted as a table, so I took the plunge.

I have attached an image of a section of my table headers.

I was using the below VBA code, but when using it now it is a table, it throws out every single December in my sheet. Is there anyway I can change the code to just have the current month? Also is there a way to code next month instead of current month?

Sub Create_Backing()
'
' Create_Backing Macro
' Create the backing for approval purposes on a spreadsheet
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Dim rng As Range
    Dim cell As Range
    
'   Designate which cells to check (all in one row)
    Set rng = Range("$C$5:$DS$5")
    
'   First, unhide all columns
    rng.EntireColumn.Hidden = False
    
'   Loop through cells and hide columns
    For Each cell In rng
'       Check to see if current month/year
        If Format(cell, "mmm-yy") <> Format(Date, "mmm-yy") Then
'           Hide if not equal
            cell.EntireColumn.Hidden = True
        End If
    Next cell
    
' Hide difference and comments
   
   Dim c As Range

    For Each c In Range("A5:DY5").Cells
        If c.Value = "Difference" Then
            c.EntireColumn.Hidden = True
        End If
   
   Next c
   
   Dim d As Range

    For Each d In Range("A5:DY5").Cells
        If d.Value = "Remaining PO Value" Then
            d.EntireColumn.Hidden = True
        End If
   
   Next d
   
   Dim e As Range

    For Each e In Range("A5:DY5").Cells
        If e.Value = "Comments" Then
            e.EntireColumn.Hidden = True
        End If
    
    Next e
   
Dim f As Range

    For Each f In Range("A5:DY5").Cells
        If f.Value = "PO No." Then
            f.EntireColumn.Hidden = True
        End If
    
    Next f
    
' Copy sheet to new book

    ActiveSheet.Copy
    
End Sub

Any help would be greatly appreciated!

Many Thanks & Kind Regards

Clazgre

 

[kevin9999]

Please try the following on a copy of your workbook. Only looking as the months (current and next month options).

Sub This_Month()
    Application.ScreenUpdating = False
    Dim c As Range, d As Date, LCol As Long
    d = Format(Date, "mmm-yy")
    LCol = Cells(5, Columns.Count).End(xlToLeft).Column
    
    Range("5:5").EntireColumn.Hidden = False
    For Each c In Range(Cells(5, 3), Cells(5, LCol))
        If c <> d Then c.EntireColumn.Hidden = True
    Next c
    Application.ScreenUpdating = True
End Sub

Sub Next_Month()
    Application.ScreenUpdating = False
    Dim c As Range, d As Date, LCol As Long
    d = Format(WorksheetFunction.EDate(Date, 1), "mmm-yy")
    LCol = Cells(5, Columns.Count).End(xlToLeft).Column
    
    Range("5:5").EntireColumn.Hidden = False
    For Each c In Range(Cells(5, 3), Cells(5, LCol))
        If c <> d Then c.EntireColumn.Hidden = True
    Next c
    Application.ScreenUpdating = True
End Sub

 

[CLAZGRE]

Perfect, thank you Kevin9999!

 

[kevin9999]

Perfect, thank you Kevin9999!

Happy to help, and thanks for the feedback