PDA

View Full Version : Excel vba copy paste with formulas



Kaniguan1969
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))
Else
MsgBox "Value " & TargetDate & " not found." & vbCrLf & "Processing terminated."
Exit Sub
End If
Stop

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.

westconn1
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