AutoFill error

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