Runtime error 1004 in VBA with AutoFill

Hi,

This is actually my first post to the forums, also pretty much making my first cuts in VBA so bear with me please

Using Excel 2010;

I'm editing two worksheets in a multisheet workbook, transfering data from "Sheet2" to "Sheet1" using copy-paste.
I have an unknown amount of data in a list with headers in columns A-J in Sheet2, which I will need to copypaste to Sheet1.

I will then add formulas to columns H and J, starting from row 4, the first row below headers. I will then need to autofill the formulas to the last row.

Amount of rows can be different everytime and can change between 1-221 at the moment.

I get Runtime error 1004 at lines with autofill. Otherwise code runs fine. RT error is my major obstacle, but I don't mind suggestions about optimising code as I'm a new hand in VBA

Sub VauhditusAlennusPohja()

' Clears content from Columns A to K from Sheet1 (destination sheet)
  Sheets("Sheet1").Select
    ActiveSheet.Range("A:K").Select
    Selection.Clear

' Selects relevant columns in Sheet2  and copy pastes to destination sheet
    Sheets("Sheet2").Select
    ActiveSheet.Range("A:J").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

' Sets formula in column H, row 4 and autofills to predefined range (would prefer to any range with data)
   ActiveSheet.Range("H4").Select
   ActiveCell.FormulaR1C1 = "=1-(RC[-1]/RC[1])"
   Selection.AutoFill Destination:=Range("H4:K220") ' error line, causes runtime 1004

' Writes text string to K3 and sets formatting
 ActiveSheet.Range("K3").Select
    Application.CutCopyMode = False
    With Selection
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "A - as. hinta per kpl"

' Sets borders around K3
    Sheets("Sheet1").Select
    ActiveSheet.Range("K3").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

' Sets formula to K4 and autofills to predefined range (causes runtime 1004 as autofill from H4)
   ActiveSheet.Range("K4").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-5]"
    Selection.AutoFill Destination:=Range("K4:K220") ' errorline


   ActiveSheet.Range("I3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.Range("I2").Select
    ActiveCell.FormulaR1C1 = "Syötä hinta sarakkeeseen I"
    ActiveSheet.Range("A1").Select

End Sub

Help much appreciated!

Br, Arkki

Originally Posted by Norie

Does this work for the first AutoFill?

     Range("H4:K220").FormulaR1C1 = "=1-(RC[-1]/RC[1])"

Hi,

for some reason it does not enter the formula at all.

In reference to previous post by Norie, the problem is not entering the formula to the range. The issue is that it should autofilled only to the last row with data.

That last row is usually row 220, but can be anything between row 4-220. End users don't want the autofill by default to whole max range.

Right now the code works fine otherwise. But users dont want to fill down the formula by themselves, it should come from the macro.

Many thanks,

Arkki

Hi here's the sample workbook, code is stored in ThisWorkBook.

Br,

Arkki