We help IT Professionals succeed at work.

Run-time error '1004': autofill method of range class failed.

1,696 Views
Last Modified: 2017-03-29
Hi all -
Need a quick fix for this macro.
It was working & has quit.  I commented out the original code & tried the "lastrow".  Obviously, I have screwed up how it should be - and my VB skills are bad @ best...
Any help would be greatly appreciated!
Thank you!


Dim lastrow As Long

lastrow = Range("Y65000").End(xlUp).Row


    'Copy formulas
    Sheets("DDC Checklist").Select
    Range("Y7:AI7").Select
                          'Selection.AutoFill Destination:=Range("Y7:AI506")
    Selection.AutoFill Destination:=Range("Y7:Y" & lastrow)
    Range("Y7:AI506").Select
       
   
    'Select Range A1 on DDC Sheet
    Range("A1").Select
   
    'Select Range A1 on Input Sheet
    Sheets("Input").Select
    Range("A1").Select
     
   
End Sub
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Could you please post your full code?

Author

Commented:
Sub AddNewLineNewJob()

    Range("A4").Select
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.RowHeight = 18
    Rows("5:5").Select
    Selection.Copy
    Range("A4").Select
    ActiveSheet.Paste
    Range("D4:X4").Select
    Application.CutCopyMode = False
    Selection.ClearContents
   
    'Run macro to copy formulas on DDC sheet
   
    copy_formulae_DDC_Checklist
    Range("D4").Select

End Sub

Sub copy_formulae_DDC_Checklist()



'This macro updates the formulas on the 'DDC Checklist' sheet when a new row is inserted on the Input sheet.

Dim lastrow As Long

lastrow = Range("Y65000").End(xlUp).Row

    'Copy formulas
    Sheets("DDC Checklist").Select
    Range("Y7:AI7").Select
              'Selection.AutoFill Destination:=Range("Y7:AI506")
    Selection.AutoFill Destination:=Range("Y7:Y" & lastrow)
    Range("Y7:AI506").Select
     
    'Select Range A1 on DDC Sheet
    Range("A1").Select
   
    'Select Range A1 on Input Sheet
    Sheets("Input").Select
    Range("A1").Select
     
End Sub
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Try below:
Sub AutoFill()
Dim Ws As Worksheet
Dim lastRow As Long
Set Ws = Worksheets("DDC Checklist")
lastRow = Ws.Range("Y" & Rows.Count).End(xlUp).Row
Ws.Range("Y7").AutoFill Destination:=Ws.Range("Y7:AI" & lastRow)
End Sub

Open in new window

Author

Commented:
Same error...
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Are you running my code separately or you are calling from your original code?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Paste above code in your module and run macro AutoFill

Author

Commented:
I called it from the new line macro.  And I added the indented line...

Sub AutoFill()
Dim Ws As Worksheet
Dim lastRow As Long
Set Ws = Worksheets("DDC Checklist")
lastRow = Ws.Range("Y" & Rows.Count).End(xlUp).Row
       Ws.Range("Y7:AI7").Select
Ws.Range("Y7").AutoFill Destination:=Ws.Range("Y7:AI" & lastRow)
End Sub
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Try below:
Sub AutoFill()
Dim Ws As Worksheet
Dim lastRow As Long
Set Ws = Worksheets("DDC Checklist")
lastRow = Ws.Range("Y" & Rows.Count).End(xlUp).Row
Ws.Range("Y7:AI7").AutoFill Destination:=Ws.Range("Y7:AI" & lastRow)
End Sub

Open in new window

Author

Commented:
It doesn't do anything w/out the select.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Have you tried with select?

Author

Commented:
I will.  Have to run to a meeting
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You try without Select

Author

Commented:
It was a KB that caused the issue
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
What is KB?
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Sorry, problem was not from my side, I did gave you solution.
Please assign points reasonably.

Author

Commented:
I wasn't trying to not give you points.  What I was trying to do was alert others of a possible issue that was not code related.  i don't need the points...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions