Macro - Copy and Paste to a Specific Row based on the input in a cell

**usc1382** · 05-22-2012, 04:23 PM

I am trying to write a macro that will copy and paste a formula to a specific row based upon my input in a certain cell. Therefore, one time I might need to have the macro copy and paste to row 25 and the next time the macro might be required to paste the formula to row 50. I have attached the file including the basic macro. Any help would be greatly appreciated. Thank you.

**Pichingualas** · 05-22-2012, 04:50 PM

Change your macro to this:

Sub Copy_Paste()
' Copy_Paste Macro

    Application.ScreenUpdating = False
    
    Range("B3:B" & Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row).ClearContents
    Range("B2").Copy
    Range("B2:B" & Range("D1").Value).PasteSpecial
    Range("A1").Select
    
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True

End Sub

I hope that helps.

**Pichingualas** · 05-22-2012, 04:53 PM

I added a line that will delete the rows in column B below B2, so that if you go from 50 to 25 you don't keep the lines below 25. I also got rid of most of the selections because it's best not to use them in my experience.

**RaulRodriguez** · 05-22-2012, 04:55 PM

This is all the code you need

Sub Copy_Paste()
Dval = Range("D1").Value

Range("B2:B" & Dval).FormulaR1C1 = "=VLOOKUP(RC1,Data!C1:C2,2,FALSE)"

End Sub

**HSV** · 05-22-2012, 04:56 PM

Or:

Sub Copy_Paste()
With Sheets("forumlas")
 If .Range("B2") > 0 Then
   .Columns(2).SpecialCells(-4123).Offset(1).ClearContents
  End If
  .Range("B2").Resize(.Range("D1") - 1).FormulaR1C1 = "=VLOOKUP(RC1,Data!C1:C2,2,FALSE)"
 End With
End Sub