• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 818
  • Last Modified:

Using Autofill in Excel VBA

I keep getting "Run-time error 1004", AutoFill method of range class failed"
I just want to automatically autofill Columns A to N to the using the latest non-empty row.

How should I revise the following?

Sub sadikyarim()
Dim lastrow As Long
     
    lastrow = Worksheets("sheet2").Range("N8").End(xlDown).Row
    With Worksheets("Sheet2").Range("A8:N8").End(xlDown)
        .AutoFill Destination:=Range("A8:N" & lastrow&)
    End With

  End Sub
0
awesomejohn19
Asked:
awesomejohn19
  • 2
2 Solutions
 
nutschCommented:
How about?

Sub sadikyarim()
Dim lastrow As Long
     
    lastrow = Worksheets("sheet2").Range("N8").End(xlDown).Row
    Worksheets("Sheet2").Range("A8:N" & llastrow).formular1c1=Worksheets("Sheet2").Range("A8").formular1c1

  End Sub 

Open in new window



In your code, the issue is the last & 

        .AutoFill Destination:=Range("A8:N" & lastrow&)

should be

        .AutoFill Destination:=Worksheets("Sheet2").Range("A8:N" & lastrow)

Since the destination range is not defined as a range of Worksheets("Sheet2"), if your activesheet is anything else than Sheet2, it will also fail.

Thomas
0
 
Rory ArchibaldCommented:
You should also be autofilling from row 8, not from Range("A8:N8").End(xlDown):

With Worksheets("Sheet2").Range("A8:N8")
        .AutoFill Destination:=Range("A8:N" & lastrow&)
    End With

Open in new window

0
 
awesomejohn19Author Commented:
I modified the code nutsch posted and it works.

Sub sadikyarim()
Dim lastrow As Long
     
    lastrow = Worksheets("sheet2").Range("N8").End(xlDown).Row
    lastrow = lastrow + 1
    Worksheets("Sheet2").Range("A" & lastrow & ":N" & lastrow).FormulaR1C1 = Worksheets("Sheet2").Range("A" & lastrow - 1 & ":N" & lastrow - 1).FormulaR1C1
Range("A" & lastrow) = Range("A" & lastrow) + 1
  End Sub
0
 
awesomejohn19Author Commented:
I did not address the question correctly. I should have said I would like to autofill using the last row instead of the first row of the series.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now