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