Excel Macro to Copy Formulas & Copy/Paste Values

Question

I am trying to create an Excel Macro to copy formulas along a used range and then copy the form and paste values.

I was thinking that I house the formulas that need to be copied in a template file in row 1. Users can then input data into as many rows as they please, and the macro will copy down all formulas to all used rows, and then copy/paste values of the entire sheet.

Can anyone help with writing this? I have attempted to write it myself but haven't been able to get very far.

Thanks!

EDIT- - I have gotten the copy part down I believe. Now I just need to copy/paste values on the majority of the sheet, starting from row 4 down.

    Sub Forecast()

Application.ScreenUpdating = False


  ' Get the last row on the sheet - store as variable
  Dim LastRow As Integer
  LastRow = Range("A1").End(xlDown).Row

  ' Copy cells K3:AY3  to  cells K4:AY[LastRow]
  Range(Cells(3, 11), Cells(3, 51)).AutoFill _
         Destination:=Range(Cells(3, 11), Cells(LastRow, 51))

Application.ScreenUpdating = True

End Sub

This seems like an odd request. A simple table (Insert -> Table) will auto-copy down formulas for each row. And generally you don't need the cells to be values instead of formulas unless the formulas' source values will never change and you need to speed up the workbook because it has become huge and sluggish. What's the purpose for this? — tigeravatar
Also "I have attempted to write it myself but haven't been able to get very far." Please edit your post to include your code so far, it will go a long way to getting this site to assist you if you show your work. — tigeravatar
Yea, many complex formulas and I don't want to bog down the sheet. — W. Hesler

John Alexiou John Alexiou · Accepted Answer · 2017-10-11T20:29:45

To copy the values for a variable number of rows between two locations do the following:

Dim r_src as Range, r_dst as Range
' Set to the top cell of the source
Set r_src = Sheet1.Range("A2")

Dim n as Long
' Count the non-empty cells
n = Sheet1.Range(r_src, r_src.End(xlDown)).Rows.Count

' Set the range to include all 'n' cells using the `.Resize()` command
Set r_src = r_src.Resize(n,1)
' Set 'n' values in the destination sheet also
Set r_dst = Sheet2.Range("A2").Resize(n,1)

'This actually copies the range as values in one swoop
r_dst.Value = r_src.Value

To copy the formulas you can use

r_dst.FormulaR1C1 = r_src.FormulaR1C1

Excel Macro to Copy Formulas & Copy/Paste Values

1 Answers