AutoFill Method of Rang Class Failed Error

humsboyle

Board Regular
Joined
Aug 4, 2010
Messages
53
Hi All,

I have been using the following code to Autofill multiple rows at once and have used it multiple times but i am not getting an error
Code:
Dim LastA As Long, LastG As Long
    
    LastA = Range("A" & Rows.Count).End(xlUp).Row
    LastG = Range("G" & Rows.Count).End(xlUp).Row
    
    Range("A" & LastA & ":F" & LastA).AutoFill _
        Destination:=Range("A" & LastA & ":F" & LastG)

I am needing the code to copy from the last row between columns A to F (Contains formula) as far down as the end of Column G. Column G is always longer as data is pasted into this cell from previous code.

When i run the above it works perfect in that it autofills all columns to the correct row level of G however once its finished i recieve the error appear on screen.
"Run-Time Error '1004':
AutoFill method of Range class failed"

Even if i add other macros linked into it i.e
Code:
Sub AutofillNiacs()
' Autofill formula Niacs
Dim LastA As Long, LastG As Long
    
    LastA = Range("A" & Rows.Count).End(xlUp).Row
    LastG = Range("G" & Rows.Count).End(xlUp).Row
    
    Range("A" & LastA & ":F" & LastA).AutoFill _
        Destination:=Range("A" & LastA & ":F" & LastG)
    
    
Sheets(Array("NiacsDay", "NiacsUpdate")).Select
   
    ActiveWindow.SelectedSheets.Visible = False
 NewDateNiacs
End Sub

This full code will run to the end and do what it needs and then the error message will pop up.

Within the spreadsheet i have the same code used a number of times an none give the error.

Any help would be great thanks

Steve
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I think that this may be the cause

Code:
Sheets(Array("NiacsDay", "NiacsUpdate")).Select
   
    ActiveWindow.SelectedSheets.Visible = False

If either or both of those sheets are hidden then you'll get a 1004 error when you try to select them. Try instead

Code:
Sheets(Array("NiacsDay", "NiacsUpdate")).Visible = False
 

humsboyle

Board Regular
Joined
Aug 4, 2010
Messages
53
Hi VoG,

Cheers for looking at my issue
unfortunatley i am still getting the same issue and Excel still highlights

Code:
Range("A" & LastA & ":F" & LastA).AutoFill _
        Destination:=Range("A" & LastA & ":F" & LastG)

Thanks
Steve
 

humsboyle

Board Regular
Joined
Aug 4, 2010
Messages
53
Searched on the web and still unable to find any solution -
if anybody else has any ideas - let me know

cheers]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,181
Members
409,856
Latest member
MAO
Top