You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan Correa wrote:

Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"", ""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec" ")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa

--

Dave Peterson