VBA - Hide Columns and Copy to a New Workbook

[CLAZGRE]

Hello,

I am wondering if someone can help me. I am a novice at writing VBA codes and I am having real problems with a macro I inherited not working. I would like it to hide all month columns apart from next month. The macro seems to just be hiding the current month and not all of the other months.

Sub HideColumns()



Dim rng As Range

Dim cell As Range

Dim LCol As Long

Dim d As Date



' Designate which cells to check (all in one row)

Set rng = Range("$B$6:$BW$6")



' 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

d = Format(WorksheetFunction.EDate(Date, 1), "mmm-yy")

LCol = Cells(5, Columns.Count).End(xlToLeft).Column'

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:CV7").Cells

If c.Value = "Difference" Then

c.EntireColumn.Hidden = True

End If



Next c



Dim d As Range



For Each d In Range("A5:CV7").Cells

If d.Value = "Comments" Then

d.EntireColumn.Hidden = True

End If



Next d





Dim e As Range



For Each e In Range("A5:CV7").Cells

If e.Value = "Remaining PO" Then

e.EntireColumn.Hidden = True

End If



Next e





' Copy sheet to new book



ActiveSheet.Copy





End Sub

Any help would be very much appreciated!


Many Thanks!

CLAZGRE

 

[rlv01]

Perhaps something like this.

Sub HideColumns()
    Dim rng As Range
    Dim cell As Range
    Dim dt As Date
    Dim c As Range
  
    Dim NextMonth As Date, ColumnMonth As Date
  
    ' Designate which cells to check (all in one row)
    Set rng = Range("$B$6:$BW$6")
  
    ' First, unhide all columns
    rng.EntireColumn.Hidden = False
  
    'Set next month (column to remain visible)
    NextMonth = DateSerial(Year(Date), Month(Date) + 1, 15)
  
    ' Loop through cells and hide columns
    For Each cell In rng
      
        ' Check to see if cell contains a date
        If IsDate(cell.Value) Then
            'Get date, create comparison value (ColumnMonth)
            dt = cell.Value
            ColumnMonth = DateSerial(Year(dt), Month(dt), 15)
          
            'Compare column year/month to next year/month
            If ColumnMonth <> NextMonth Then 'Hide if not equal
                cell.EntireColumn.Hidden = True
            End If
        End If
    Next cell
  
    ' Hide difference and comments
    For Each c In Range("A5:CV7").Cells
        If Not c.EntireColumn.Hidden Then
            If c.Value = "Difference" Or c.Value = "Comments" Or c.Value = "Remaining PO" Then
                c.EntireColumn.Hidden = True
            End If
        End If
    Next c
  
    ' Copy sheet to new book
    ActiveSheet.Copy
End Sub

 

[CLAZGRE]

Perfect! Thanks you so much rlv01!