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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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