Avatar of jennifer george
jennifer george
Flag for United States of America asked on

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

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
Microsoft ApplicationsMicrosoft OfficeMicrosoft ExcelVB ScriptVBA

Avatar of undefined
Last Comment
jennifer george

8/22/2022 - Mon
Shums Faruk

Could you please post your full code?
jennifer george

ASKER
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
Shums Faruk

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
jennifer george

ASKER
Same error...
Shums Faruk

Are you running my code separately or you are calling from your original code?
Shums Faruk

Paste above code in your module and run macro AutoFill
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jennifer george

ASKER
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
Shums Faruk

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

jennifer george

ASKER
It doesn't do anything w/out the select.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Shums Faruk

Have you tried with select?
jennifer george

ASKER
I will.  Have to run to a meeting
Shums Faruk

You try without Select
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jennifer george

ASKER
It was a KB that caused the issue
Shums Faruk

What is KB?
SOLUTION
jennifer george

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
jennifer george

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk

Sorry, problem was not from my side, I did gave you solution.
Please assign points reasonably.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jennifer george

ASKER
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...