Formatting Month display via macro

[PCLIVE]

I have weveral worksheets that have the months listed across the top of the
table. The months are listed in a three letter format (ex. - Jan Feb Mar
etc.)
These columns shift each month. However, because some worksheets get
updated and others don't, one worksheet's columns may not get shifted when
others do.

If I type "Jan" in A1 and then fill to the right through "Dec", it fills
with 3-letter formats. However, when the first cell is "May", it fills
using the full month name. Is there a way to reformat these cells to the
3-letter format of the month name? I'm doing all of this (copying,
pasting/fill right) via a macro.

I'm using the following code where Q1 = the month name in a 3-letter format

Range("B25").Select
Selection.Value = Sheets("Data").Range("Q1").Value
Selection.AutoFill Destination:=Range("B25:M25"), Type:=xlFillDefault


Thanks in advance,
Paul

 

[Guest]

Instead of

Selection.Value = Sheets("Data").Range("Q1").Value

You may try

Selection.Value = format(Sheets("Data").Range("Q1").Value, "mmm")

Not tested.

 

[Bob Phillips]

Hi Paul,

Try this

aryList = Application.GetCustomListContents(3)
iStart = Application.Match(Sheets("Data").Range("Q1").Value, aryList, 0)
With Sheets("Data").Range("B25")
.Value = aryList(iStart)
iSecond = iSecond + 1
If iSecond = 13 Then iSecond = 1
.Offset(0, 1).Value = aryList(iSecond)
.Resize(, 2).AutoFill Destination:=.Resize(, 12),
Type:=xlFillDefault
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)