Clearing filters, copying a range, and then pasting a formula down a row Novice coder here. I have the following code to perform a few actions like clearing filters, copying a range, and then pasting a formula down a row till the last line.

It works perfectly, but it takes longer than a minute to complete since I am addressing each in my for next loop individually. Would anyone know how to speed this up by using a range call instead of a cell call?

My constraint is that I must start on row six of the column selected and autofill to the bottom. The same with the paste values of the column to the left. 

 Sub testingme2()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim myvalue As Double, lastrow As Long, paster As String

lastrow = Worksheets("x").Cells(Rows.Count, 1).End(xlUp).Row
myvalue = Worksheets("y").Cells(6, 2).Value
paster = "formula"

If Sheets("x").FilterMode Then
    Sheets("x").Cells.AutoFilter
End If

Sheets("x").Select
Range("A6:AG5000").Select
Selection.Copy
Sheets("z").Select
Range("A6").Select
Selection.PasteSpecial xlPasteValues
Sheets("x").Select

For i = 6 To lastrow
    Worksheets("x").Cells(i, (myvalue) + 20).Copy
    Worksheets("x").Cells(i, (myvalue) + 20).PasteSpecial xlPasteValues
    Worksheets("x").Cells(i, (myvalue) + 21).Cells.FormulaR1C1 = paster
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
This should be much faster:


We get rid of the .Select and .Activate. They will slow the process down because it is telling excel to do something, that takes time.
Since we only want the values we assign them directly avoiding the clipboard.  Again just the moving of data into the clipboard takes an extra step and then moving them out again is another step.  We can do it in one.
When using R1C1 we can put the formula in bulk, so no looping.  The R1C1 references will adjust accordingly.
Using the With block and proper . notation allows us to limit typing the sheet over and over again, but still allowing the proper parentage to be assigned.




Sub testingme2()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim myvalue As Double, lastrow As Long, paster As String

lastrow = Worksheets("x").Cells(Rows.Count, 1).End(xlUp).Row
myvalue = Worksheets("y").Cells(6, 2).Value
paster = "formula"

If Sheets("x").FilterMode Then
    Sheets("x").Cells.AutoFilter
End If


Sheets("Z").Range("A6:AG5000").Value = Sheets("x").Range("A6:AG5000").Value



With Worksheets("x")
    .Range(.Cells(6, (myvalue) + 20), .Cells(lastrow, (myvalue) + 20)).Value = .Range(.Cells(6, (myvalue) + 20), .Cells(lastrow, (myvalue) + 20)).Value
    .Range(.Cells(6, (myvalue) + 21), .Cells(lastrow, (myvalue) + 21)).FormulaR1C1 = paster
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub