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