J
Juan Correa
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
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