Change Range To Recorded Macro

[Dazzawm]

I have recorded a macro that I will run every time I open the spreadsheet. Problem is that the range will change each time i run it. What do I need to change in the macro to accommodate the extended range each time please? It will also need to be run on sheet 1 each time looking at sheet 4.

Sub Macro2()
'
' Macro2 Macro
'

'
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],Sheet4!C[-27]:C[-26],2,0)"
    Selection.AutoFill Destination:=Range("AB2:AB437")
    Range("AB2:AB437").Select
    Columns("AB:AB").EntireColumn.AutoFit
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[SunnyAlv]

like this ?

Sub Dazzawm()

Dim ls As Long


ls = Range("K" & Rows.Count).End(xlUp).Row

    With Range("AB2:AB" & ls)

    .Formula2 = "=VLOOKUP(K2,Sheet4!A:B,2,0)"
    .Copy
    .PasteSpecial xlPasteValues
    .EntireColumn.AutoFit

    End With
 
Application.CutCopyMode = False

End Sub

 

[Dazzawm]

like this ?

Sub Dazzawm()

Dim ls As Long


ls = Range("K" & Rows.Count).End(xlUp).Row

    With Range("AB2:AB" & ls)

    .Formula2 = "=VLOOKUP(K2,Sheet4!A:B,2,0)"
    .Copy
    .PasteSpecial xlPasteValues
    .EntireColumn.AutoFit

    End With
 
Application.CutCopyMode = False

End Sub

That seems to do it, much obliged squire.

 

[Alex Blakenburg]

As the next step you should be able to

Replace:

    .Copy
    .PasteSpecial xlPasteValues

With:

    .Value = .Value