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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,453
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top