Excel Macro - Copying Vlookup formula down to last cell

[wardie]

Currently I am trying to automate a report build that can have a different number of rows each time it is run (weekly)

Column A has the list of devices found.

Columns B & C have data but not consistent (lots of blanks).

Columns D - T are vlookups that I would manually drag down to the last cell of Column A each time.

I'm trying to macro the copying of the vlookup formula to the last row but am stuck trying to find the code to do my task.

My current bad code for this copies down to the last row of the spreadsheet.

Sheets("ServerList").Activate
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Working_FACTS!C[-3]:C[-1],3,0)"
Range("D2").Select
Selection.Copy
* Range(ActiveCell, ActiveCell.End(xlDown)).Select
ActiveSheet.Paste
Range("D2").Select
* Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The lines with the * are the ones I need to fix. I need to capture the last cell # in Column A and transpose it to my Range.Select

Anybody with greater smarts than me like to suggest the easy fix??

Many Thanks

Wardie

 

[VoG]

Try

Dim LR As Long
Sheets("ServerList").Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-1],Working_FACTS!C[-3]:C[-1],3,0)"
Range("D2").AutoFill Destination:=Range("D2:D" & LR)

 

[wardie]

Thank you VoG, that has solved my problem.

I knew it would be easy but had hit a mental road block