AutoFill error

Hi,

I have been getting a "Run-time error '1004': AutoFill method of Range class failed" on a macro. There only seems to be problems when I assign the macro to a button. When I run the macro directly everything works fine.

Here's the bit of code in question:

Worksheets("Data").Visible = xlSheetVisible
    
    Sheets("Data").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A13"), Type:=xlFillDefault
    Range("A1:A13").Select
    Selection.AutoFill Range("A1:J13"), Type:=xlFillDefault
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A15").Select
    Selection.AutoFill Destination:=Range("A15:A29"), Type:=xlFillDefault
    Range("A15:A29").Select
    Selection.AutoFill Destination:=Range("A15:J29"), Type:=xlFillDefault
    Range("A15:J29").Select
    
    Worksheets("Data").Visible = xlSheetVeryHidden

It fails at line 6 where it first tries to autofill. I know that the code could be simplified, this is how Excel gave it to me when I recorded it. I have tried changing .Select to .Activate and I have tried getting rid of .Select and Selection. by merging Range and Autofill and I tried adding Sheets("Data"). before all the Range lines.

I'm new at VBA and this is pretty frustrating since it worked before I assigned to a button, so any help would be greatly appreciated.

Thanks,
Mark

Hello Mark,

Welcome to the Forum!

I revised your macro. You can attach this macro to a button or run it directly.

Sub Macro1()

  With Worksheets("Data")
    .Visible = xlSheetVisible
      .Range("A1").Value = 1
      .Range("A1:A13").FillDown
      .Range("A15").Value = 1
      .Range("A15:A29").FillDown
    .Visible = xlSheetVeryHidden
  End With

End Sub

Sincerely,
Leith Ross

Thanks for the help Leith. I pasted you code into mine with the same result. I was assuming the problem lie with the portion of the code that isn't working. Now I'm not so sure so here is code in its entirety.

I have 2 primary sheets that create copies of a template. The template is full of drop-down menus positioned over cells with formulas to show what was chosen. All the data for the drop-down menus are kept on a hidden sheet "Data". When the user finishes filling out the "Template" sheet by selecting values from the drop-down menus, the user will click a button on the sheet. The button should copy the template to a new sheet, erase all cells and buttons and menus, copy just the data from the template over, rename the new sheet, reset the data in the "Data" sheet, and save the file.

Sub Save_Reset()

    Dim sheet_name As String
    
    Sheets("Template").Select
    Sheets("Template").Copy After:=Sheets(2)
    Range("A1:J30").Select
    Selection.ClearContents
    Range("C4:E4").Select
    
    ActiveSheet.Shapes("Drop Down 14").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 12").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 13").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 8").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 9").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 20").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 3").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 31").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 35").Select
    Selection.Delete
    ActiveSheet.Shapes("Drop Down 39").Select
    Selection.Delete
    
    Sheets("Template (2)").Select
    Range("A1:J30").Select
    Selection.ClearContents
    Sheets("Template").Select
    Range("A1:J30").Select
    Selection.Copy
    Sheets("Template (2)").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
    
Sub Macro1()

  With Worksheets("Data")
    .Visible = xlSheetVisible
      .Range("A1").Value = 1
      .Range("A1:A13").FillDown
      .Range("A15").Value = 1
      .Range("A15:A29").FillDown
    .Visible = xlSheetVeryHidden
  End With

End Sub
    
    Sheets("Template (2)").Select
    sheet_name = Range("AG1")
    ActiveSheet.Name = sheet_name
    
    Sheets("Template").Select
    Range("A1:B1").Select
    Sheets(sheet_name).Select
    Range("A1:B1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.save
    
End Sub

Again, thanks for the help,

Mark Bottorff

Bump to the top.

Hello Mark,

Would you post your workbook so I can review it?

Sincerely,
Leith Ross