autofill formula's and paste as value

[NatureGreen]

Greetings

I'm a vba beginner and trying to make a macro for my monthly Excel tasks.

Every month i get a report. I add a colum but can't do that with vba cause the report has a merged cell in the first row over half of our sheet. So i solved that with adding cells. Got 3 sheets open while working on this one so i need to use ActiveSheet.

Sub McrTest()
ActiveSheet.Range("F5", ActiveSheet.Range("F5").End(xlDown)).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Range("F5").Select
ActiveCell.Value = "NewColumName"
ActiveSheet.Range("F6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,10)"

So far so good. But now i need to autofill the formula down to the last row too and then set the colum to just value.

i have to do this with 2 colums in this sheet cause i also have to add a concatenate formula.

Thanks in advance.

 

[James006]

Hello,

You could test following :

Sub McrTest()
Dim last As Long
last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F5").Value = "NewColumName"
    Range("F6").FormulaR1C1 = "=MID(RC[-1],1,10)"
    Range("F6").Copy Destination:=Range("F7:F" & last)
End Sub

Hope this will help

 

[NatureGreen]

It worked except the last line to copy the formula to the last line.

 

[Fluff]

How about

Sub Naturegreen()
   Columns(6).Insert
   Range("F5").Value = "New Column"
   With Range("F6", Range("E" & Rows.Count).End(xlUp).Offset(, 1))
      .FormulaR1C1 = "=mid(rc[-1],1,10)"
      .Value = .Value
   End With
End Sub

 

[NatureGreen]

That works!

Thank you very much. ^^