Copying formula to a range of cells and converting it to a value

Question

I am trying to copy the formula from D1 and paste it till last row(26446) value may increase

Dim lrow As Double
lrow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

   Range("D2").Copy Destination:=Range("D2:D" & lrow)
   Range("D2" & "lrow").Copy   'THIS LINE SHOWS 1004 error
   Range("D2:D" & "lrow").PasteSpecial Paste:=xlPasteValues

How to solve this

Answer 1

You have already got two excellent answers and hence this is not an answer but an enhancement to your code.

1. You don't need to declare the row variable as Double. Long is good enough. You may want to read up on Data type HERE

2. Your code can be reduced to this

Code

BTW, Your question says D1 but your code is taking D2 into consideration. So I am going with D2. If it is D1 then replace D2 by D1 below.

Sub Sample()
    Dim lrow As Long

    With Sheets("Sheet2")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("D2:D" & lrow).Formula = .Range("D2").Formula
        .Range("E2:E" & lrow).Formula = .Range("E2").Formula
        .Range("F2:F" & lrow).Formula = .Range("F2").Formula
        .Range("G2:G" & lrow).Formula = .Range("G2").Formula

        .Range("D2:G" & lrow).Value = .Range("D2:G" & lrow).Value
    End With
End Sub

Answer 2

Anything within quotes will be considered as a string so "lrow" is considered as a String and not a variable.

It should be

Range("D" & lrow).Copy

or whatever range you are trying to copy. I think, you are trying

Range("D2:D" & lrow).Copy

The other simple alternative to

Range("D2:D" & "lrow").Copy  
Range("D2:D" & "lrow").PasteSpecial Paste:=xlPasteValues

is

Range("D2:D" & lrow).Value = Range("D2:D" & lrow).Value