Hi Team,
I have a worksheet where I work with 3 consecutive months of the year. I have code to find the last day of the month, then enter a blank column to separate all of the months. This code below, kindly provided by @Peter_SSs works extremely well. However, when the 3 consecutive months spill over to a new year, i.e. November, December and January, the code does not separate December and January. Similarly with December, January and February, it does not separate them at all. I've tried to add an addition year's worth of months to the end of the "AllHdrs = Split..." line of code, but this doesn't seem to work. Any suggestions?
Thanks,
Brad.
I have a worksheet where I work with 3 consecutive months of the year. I have code to find the last day of the month, then enter a blank column to separate all of the months. This code below, kindly provided by @Peter_SSs works extremely well. However, when the 3 consecutive months spill over to a new year, i.e. November, December and January, the code does not separate December and January. Similarly with December, January and February, it does not separate them at all. I've tried to add an addition year's worth of months to the end of the "AllHdrs = Split..." line of code, but this doesn't seem to work. Any suggestions?
Thanks,
Brad.
VBA Code:
'Enter a blank column after the end of each month.
Dim AllHdrs As Variant, Hdr As Variant
Dim rFound As Range
AllHdrs = Split("31-Jan|29-Feb|31-Mar|30-Apr|31-May|30-Jun|31-Jul|31-Aug|30-Sep|31-Oct|30-Nov|31-Dec", "|")
For Each Hdr In AllHdrs
Set rFound = Rows(1).Find(What:=DateValue(Hdr), LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rFound Is Nothing Then Columns(rFound.Column + 1).Insert
Next Hdr