View Full Version : Excel vba copy paste with formulas

07-20-2014, 11:35 PM
Hi, I have this codes below that perform copying of records that includes the formula. May i ask your assistnace on how to do this in vba macro. thanks.

If Not rng1 Is Nothing Then
'Following line assigns to rngToCopy the Range from cell below the found rng1 _
'to the last used cell in the column
Set rngToCopy = Range(rng1.Offset(1, 0), .Cells(.Rows.Count, rng1.Column).End(xlUp))
MsgBox "Value " & TargetDate & " not found." & vbCrLf & "Processing terminated."
Exit Sub
End If

Set rngNextDestin = Range(rng1.Offset(1, 0), .Cells(.Rows.Count, rng1.Column).End(xlUp)).Offset(0, 1)
Debug.Print rngNextDestin.Address
rngNextDestin.Resize(rngToCopy.Rows.Count, rngToCopy.Columns.Count) = rngToCopy.Value --this portion will copy that includes formulas.

07-21-2014, 03:10 AM
rngToCopy.FormulaR1C1= "=rc[-1]"

copies value from one cell to left for each row, change formula to suit

Bob Phillips
07-21-2014, 06:13 AM
rngToCopy.copy rngNextDestin

will copy formulas, formats, the lot