VBA Automatic Rolling Column Headings

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using a sheet called "Monthly Profile" which profiles staff resource.

The sheet has 'Monthly' columns heading in row 7 running from column B to Q, starting with Nov 13 to Dec 14

In additon, I display the current month in cell B3 on the same sheet in the format of "mmm yy".

The monthly column headings must be in the range of two months prior to the current month, and then a further 12 months from the current month.

In it's current form, every month I have to manually change the column headings to follow the aforementioned range.

I know from the research I've done that I could use as number of formulas to automate this task, but I was wonderiong whether someone may be able to offer some guidance on whether this could be done automatically within VB, which I can incoporate into a larger script which I use to populate the sheet.

Many thanks and kind regards

[Bob Phillips]

B7: =DATE(YEAR(B3),MONTH(B3)-2,1)
C7: =EOMONTH(B7,0)+1

copy C7 across

[hobbiton73]

Hi @xld, thank you very much for taking the time to reply to my post and for the suggestion.

Could you perhaps tell me please would it be possible to do this via VB, which I can integrate into an existing script.

Many thanks and kind regards

[Bob Phillips]

With Activesheet
 
    .Range("B7").Formula = "=DATE(YEAR(B3),MONTH(B3)-2,1)"
    .Range("C7").Resize(,11).Formula = "=EOMONTH(B7,0)+1"
End With

[hobbiton73]

Hi @xld, thank you very much for coming back to me with this, and my sincere apologies for not replying sooner.

I've included your suggestion and my script works exactly how I had hoped it would.

Once again, many thanks and all the best.