Hi I have a macro which is copying and pasting a vlookup.
How do I get it top running hen the data the spreadsheet runs out? instead of giving it an line number to end on as below:
Sub vlookup()
'
' vlookup Macro
'
'
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Lookup!C[-9]:C[-6],4,FALSE)"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Lookup!C[-11]:C[-9],3,FALSE)"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Lookup!C[-12]:C[-6],7,FALSE)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Lookup!C[-13]:C[-6],8,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],Lookup!C[-14]:C[-6],9,FALSE)"
Range("N2:S2").Select
Selection.Copy
Range("N3:N6000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("N2").Select
Columns("N:N").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Range("T2").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("U2").Select
Columns("R:R").EntireColumn.AutoFit
Columns("S:S").EntireColumn.AutoFit
Range("N1").Select
End Sub
Thanks Jimmy
How do I get it top running hen the data the spreadsheet runs out? instead of giving it an line number to end on as below:
Sub vlookup()
'
' vlookup Macro
'
'
Range("N2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Lookup!C[-9]:C[-6],4,FALSE)"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Lookup!C[-11]:C[-9],3,FALSE)"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],Lookup!C[-12]:C[-6],7,FALSE)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Lookup!C[-13]:C[-6],8,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],Lookup!C[-14]:C[-6],9,FALSE)"
Range("N2:S2").Select
Selection.Copy
Range("N3:N6000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("N2").Select
Columns("N:N").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Range("T2").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("U2").Select
Columns("R:R").EntireColumn.AutoFit
Columns("S:S").EntireColumn.AutoFit
Range("N1").Select
End Sub
Thanks Jimmy