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

jennifer george
jennifer george used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
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
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Commented:
Are you running my code separately or you are calling from your original code?
ShumsManaging Director/Excel VBA Developer
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
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
Distinguished Expert 2018

Commented:
Have you tried with select?

Author

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

Commented:
You try without Select

Author

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

Commented:
What is KB?
One of the M$ KB Updates broke it.  I uninstalled the Update & now it works.
I've requested that this question be closed as follows:

Accepted answer: 0 points for jennifer george's comment #a42066184

for the following reason:

I uninstalled KB 3178690 & now it works again
ShumsManaging Director/Excel VBA Developer
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial