Excel 10 Subtotal update failure

[Hduddle]

Hi all
This is my 1st post so apologies for any errors.

It's time to stop tearing my hair out (I haven’t got much anyway!) and ask the experts:

I have a data table of which the first 3 columns are unchanging headings (Date/Description/Amount). The Final Column of the table provides a cross check for data entry.
I use an empty hidden(Dummy) column immediately before the CrossCheck column so that when I insert a new column all associated chart ranges etc. update correctly.
I use a Totals row which includes Subtotal(109,[categoryname]) in columns 4 onwards up to but excluding the Dummy Column.

I have written a simple couple of procedures to insert a new column, rename it and add the Subtotal formula. It works excellently for 1st three new columns but from the 4th column onwards the subtotal doesn’t update until another new column is inserted and then this column doesn’t update etc.
I’ve endlessly tried various work arounds, unsuccessfully, so I wonder if there is something unbelievably simple that I’m stupidly overlooking. I don’t understand how the same procedure can produce a different result after a while.
I would appreciate any advice. Tks

Option Explicit
Dim Newcat As String
Dim Row As Integer
Dim RowCount As Integer, ColCount As Integer, LastDataRow As Integer
Dim FormulaPasteRange As Range, RangeAds As Range, SumCol As Range
Public Newname As Range

Sub Insert_New_Column()

'"Dummy" is the name of a hidden column between the last visible
'category and the Xcheck cell, which allows table range to expand by entering
'a new column before the Dummy column

Application.ScreenUpdating = False
Worksheets("Income").Activate

'Insert a new column
Range("Dummy").EntireColumn.Select
        Selection.Insert Shift:=xlToRight
        
ColCount = Range("Table1").Columns.Count - 1
RowCount = Range("Table1").Rows.Count

Call NameChange ' Change Column Heading  and add formula to Total line

ActiveCell.Offset(-1, 0).Select 'Sets cursor to end of last table data cell

End Sub
Sub NameChange()
' Change name of new column and add formula in Table Total line

Range("Table1[[#Headers],[column1]]").Select
Newcat = InputBox("Enter Category")
ActiveCell.Value = Newcat

Set Newname = Range("Table1[[#Headers],[" & Newcat & "]]")
Newname.End(xlDown).Select
ActiveCell.FormulaR1C1 = "= subtotal(109,[" & Newname & "])"

End Sub

 

[jasonb75]

Interesting, I think that I added data to each new column, I'll try it with multiple empty columns and see what happens.

 

[jasonb75]

Result - basically the same as before - Alpha heading worked Bravo heading didn't until Charlie column was added etc. If I added new columns one after the other without including data until later, then none of columns sub-totalled when I added data - except Alpha.

Tried the same, still not getting the same issue, everything is working fine for me.

Have you tried the simple 'cure for all' of restarting your computer? Following that, are you able to test it on a different computer?

 

[Hduddle]

I’ve tried a restart with no change. I will try on wife’s laptop - when I can get on it!
tks

 

[Hduddle]

OK so all workbooks and procedures work fine on a different computer .... so the problem is with my Excel 10 - not the programming.
I think I've wasted enough of your valuable time so I wont bother you further.
I'll restart my computer again and investigate reinstalling Excel (It came pre-installed).

Thanks very much to you and Rory for your patience. Cheerio.