Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • 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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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