Trying to copy formula to bottom of data set with macro

[cdw1982]

I'm a noob, and have recorded a macro that copies a function to the bottom of a data set, my problem is that the number of rows is dynamic and I can figure out how to copy the formula to the bottom of the data set. Here is my code.


Sub ClientStatsMacro()
'
' ClientStatsMacro Macro
' Here is the Macro
'
' Keyboard Shortcut: Ctrl+m
'
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("J1").Select
ActiveCell.FormulaR1C1 = "First List Year"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'VLook UP'!R3C2:R18C3,2,TRUE)"
Range("J2").Select
'This below didn't work (ORIGINAL CODE FOR AUTOFILL)
'Selection.AutoFill Destination:=Range("J2:J21742")
'Range("J2:J21742").Select
'This below didn't work
'LastRow = ActiveSheet.UsedRange.Rows.Count
'Range("J2").AutoFill Destination:=Range("J2:J" & LastRow)
Columns("J:J").Select
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Columns("T:T").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("T1").Select
ActiveCell.FormulaR1C1 = "List Month and Year"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-2]&""-""&RC[-1])"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T21742")
Range("T2:T21742").Select
Columns("T:T").Select
Selection.NumberFormat = "m/yyyy"
End Sub

 

[JackDanIce]

Welcome to the board.

Try:

Sub ClientStatsMacro()
'
' ClientStatsMacro Macro
' Here is the Macro
'
' Keyboard Shortcut: Ctrl+m
'
Dim i As Long

With Columns("J:J")
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .NumberFormat = "General"
End With

Range("J1") = "First List Year"

With Range("J2")
    .FormulaR1C1 = "=VLOOKUP(RC[-1],'VLook UP'!R3C2:R18C3,2,TRUE)"
    i = Range("J" & Rows.Count).End(xlUp).Row
    .AutoFill Destination:=Range("J2:J" & i)
End With

With Columns("T:T")
    .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .NumberFormat = "m/yyyy"
End With

Range("T1") = "List Month and Year"
Range("T2").FormulaR1C1 = "=VALUE(RC[-2]&""-""&RC[-1])"
i = Range("T" & Rows.Count).End(xlUp).Row
Range("T2").AutoFill Destination:=Range("T2:T" & i)

End Sub