Copy a Formula in a Macro

  • Thread starter Thread starter KennyD
  • Start date Start date
K

KennyD

Ok, so I have this macro that makes copies of Sheet1 based on a list of
values in Sheet_2. Works great. However, I need to change a cell so that it
not only copies over the value, but inserts a formula.
The macro lines that creates the new sheets is as follows:

Set TemplateWks = Worksheets("Sheet1")
Set sh = Worksheets("Sheet1")
Set ListWks = Worksheets("Sheet_2")
With ListWks
Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)

On Error Resume Next
With ActiveSheet
.Name = myCell.Value
.Range("B4").Value = myCell.Value
.Range("C5").Value = myCell.Offset(0, -1).Value '****<= This is the
line I need to change
End With

How do I insert a quick formula into the cell "C5" so that it rounds down
the number? I am thinking that it should be something like
.Range("C5").Value = myCell.Offset(0,-1).Value
.Range("C5").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN((myCell.Offset(0,-1)),0)"

Essentially, I want it to grab the value and round it down before it inserts
it.
 
Use this line:

..Range("C5").Value = WorksheetFunction.RoundDown(myCell.Offset(0, -1).Value)

Regards,
Per
 
I changed it to the following and it worked.

..Range("C5").Select
ActiveCell.FormulaR1C1 =
WorksheetFunction.RoundDown((myCell.Offset(0, -1)), 0)

Thanks for the help.
 
Back
Top