CODEWITHSUNDEEP
vbaexcel
W. Hesler
W. Hesler

Reputation: 27

Excel Macro to Copy Formulas & Copy/Paste Values

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

Upvotes: 0

Views: 3248

Answers (1)

John Alexiou
John Alexiou

Reputation: 29244

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

Upvotes: 0

Related Questions