Prevent Autofill of Formulas in Excel Tables w/VBA

[Randall00]

Hello,

I've looked for a solution to a problem I'm experiencing in a variety of places and just can't quite seem to get the one I'm looking for. I feel like it should be pretty simple.

I have a series of nearly identical sheets that each have multiple Tables (ListObjects) - they all have a Header Row and the row directly below the header has an absolute SUM formula that sums up the values in the three rows directly below itself.

The problem is that in order to "reset" these tables to their default state for a user of the spreadsheet, I've created a VBA procedure that uses code to populate the SUM formula in that specific cell below the header in each of these tables--but each time I run it, Excel automatically fills the formula in on all of the rows in that Table, which creates a series of circular references that I do not want.

For the life of me, I can't figure out how to prevent this. There's a lot of promising options out there, but most of them just don't quite do it. I have already tried the following:

• Application.CellDragAndDrop = False
• Application.ExtendList = False
• Application.EnableAutoComplete = False

None of these seem t work. "AutoComplete" isn't the function I was looking for. "ExtendList" sounded very hopeful because it references the checkbox provided in Excel Options that says "extend data range formats and formulas", but whether that box is checked or not, the formula still gets automatically filled down the entire table.

How can I prevent Excel from doing this?

 

[Andrew Poulsom]

There's a setting under Office Button|Excel Options|Proofing|AutoCorrect Options. On the AutoFormat As You Type tab uncheck 'Fill Formulas In Tables To Create Calculated Columns'. Sorry I don't kno what it is in VBA but maybe the macro recorder will give you the syntax.

 

[Randall00]

Solved!

Thanks very much, Andrew--I'm surprised that the recorder picked it up from such an obscure location, but luckily it did:

Application.AutoCorrect.AutoFillFormulasInLists = False

 

[JARichard74]

Solved!

Thanks very much, Andrew--I'm surprised that the recorder picked it up from such an obscure location, but luckily it did:

Application.AutoCorrect.AutoFillFormulasInLists = False

An old post but exactly what I was looking for!