How to Make a VBA Macro Work with the Current Month’s Column in a Table?

[Lvxi1]

Hi,

I have a table with monthly columns (Jan, Feb, etc.), and I want my macro to automatically detect the current month and work with that column only. I'm using Month(Date) but not sure how to match it to the correct column header in the sheet. What’s the best way to make this dynamic?

Thank you for your help!

[June7]

Possibly use month number to reference column number, probably have to add an offset factor to allow for any columns preceding the months.

Another option is to normalize data structure and apply filter criteria. Maybe even migrate to a relational database like Access instead of trying to make Excel act like a database.

[jindon]

Example


Dim s$
With Sheets("sheet1").ListObjects("table1")
s = Format$(Date, "mmm") '<--- if this returns in your language, use below line.
's = Choose(Month(Date), "Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
.DataBodyRange.Columns(.ListColumns(s).Index).Select
End With