Only Use the Number of Rows from A column to Copy to B,C,D columns??

[mwalls33]

Folks please help -
Trying to create a macro using "Record Macro" but I have a static fields on my range please see below: - Those are all 350... I want the last range to be the same range as the last range from A1 column, how do I do that? thank you!!!

Selection.Copy
    Sheets("Output").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],[curentcontractor.xls]Data!C2:C3,2,FALSE)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B350")
    Range("B2:B350").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],[curentcontractor.xls]Data!C2:C8,6,FALSE)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C350")
    Range("C2:C350").Select
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],[curentcontractor.xls]Data!C2:C8,4,FALSE)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D350")
    Range("D2:D350").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],[curentcontractor.xls]Data!C2:C11,10,FALSE)"
    Range("E2").Select

 

[Fluff]

How about

   Dim Lr As Long
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   Range("B2:B" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-1],[curentcontractor.xls]Data!C2:C3,2,FALSE)"
   Range("C2:C" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-2],[curentcontractor.xls]Data!C2:C8,6,FALSE)"
   Range("D2:D" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-3],[curentcontractor.xls]Data!C2:C8,4,FALSE)"
   Range("E2:E" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-4],[curentcontractor.xls]Data!C2:C11,10,FALSE)"

 

[mwalls33]

How about

   Dim Lr As Long
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   Range("B2:B" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-1],[curentcontractor.xls]Data!C2:C3,2,FALSE)"
   Range("C2:C" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-2],[curentcontractor.xls]Data!C2:C8,6,FALSE)"
   Range("D2:D" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-3],[curentcontractor.xls]Data!C2:C8,4,FALSE)"
   Range("E2:E" & Lr).FormulaR1C1 = _
       "=VLOOKUP(RC[-4],[curentcontractor.xls]Data!C2:C11,10,FALSE)"

OMG! works like a charm! thank you sooo sooo much!!!

 

[Fluff]

Glad to help & thanks for the feedback