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]

Look at the 'incorrect' formula that the code has entered into one of the columns, what is the difference between that and the expected formula that should have been entered?

 

[Hduddle]

Look at the 'incorrect' formula that the code has entered into one of the columns, what is the difference between that and the expected formula that should have been entered?

Hi Jasonb75. Tks for reply. Answer: Absolutely nothing. It reads =subtotal(109,[Delta]). If I then add another new column the Delta column subtotal will update correctly but then the next new column won’t.

 

[jasonb75]

I see no references to calculation in your code, but the only reason that I can think of is that calculation is set, or is being set to manual and the sheet is not being calculated after the formula is added to the sheet.

Edit:- one other check, is the last name added, [Delta] referring to the correct range in the table, or is it referring to the 'Dummy' column in error?

 

[Hduddle]

I see no references to calculation in your code, but the only reason that I can think of is that calculation is set, or is being set to manual and the sheet is not being calculated after the formula is added to the sheet.

Edit:- one other check, is the last name added, [Delta] referring to the correct range in the table, or is it referring to the 'Dummy' column in error?

The calculation is set to auto not manual (the 1st columns add up correctly proving this and I haven’t added any code to switch to manual.
Yes the formula does point to correct range, not the dummy. If I sum the delta column outside the table, the correct result is available but just won’t update in the subtotal row until next column is added.

 

[RoryA]

Since you have a Table, you may as well use it directly in your code. I'd suggest something like this:

Sub Insert_New_Column()
Application.ScreenUpdating = False
Dim theTable As ListObject
Set theTable = Worksheets("Income").ListObjects("Table1")
'Insert a new column
With theTable
    Dim NewListColumn As ListColumn
    Set NewListColumn = .ListColumns.Add(.ListColumns("Dummy").Index)
    NewListColumn.Range.EntireColumn.Hidden = False
    .ListColumns("Dummy").Range.EntireColumn.Hidden = True
    
End With
    Call NameChange(NewListColumn) ' Change Column Heading  and add formula to Total line
End Sub
Sub NameChange(newCol As ListColumn)
' Change name of new column and add formula in Table Total line
Newcat = InputBox("Enter Category")
With newCol
    .Name = Newcat
    .TotalsCalculation = xlTotalsCalculationSum
End With
End Sub

 

[Hduddle]

Since you have a Table, you may as well use it directly in your code. I'd suggest something like this:

Sub Insert_New_Column()
Application.ScreenUpdating = False
Dim theTable As ListObject
Set theTable = Worksheets("Income").ListObjects("Table1")
'Insert a new column
With theTable
    Dim NewListColumn As ListColumn
    Set NewListColumn = .ListColumns.Add(.ListColumns("Dummy").Index)
    NewListColumn.Range.EntireColumn.Hidden = False
    .ListColumns("Dummy").Range.EntireColumn.Hidden = True
   
End With
    Call NameChange(NewListColumn) ' Change Column Heading  and add formula to Total line
End Sub
Sub NameChange(newCol As ListColumn)
' Change name of new column and add formula in Table Total line
Newcat = InputBox("Enter Category")
With newCol
    .Name = Newcat
    .TotalsCalculation = xlTotalsCalculationSum
End With
End Sub

 

[Hduddle]

Hi Rory A, Tks for reply - a more elegant coding than mine, as expected, .... however the result is the same - no column summing until the next column is inserted!.

 

[Hduddle]

Hmmm. I've just been back and tried again and at the moment your solution is now working.
Still doesn't explain what was going wrong in my version though.
I'll give your answer a bit more hammering and see if it really works. (I don't doubt your work - just myself!)

 

[Hduddle]

Hi Rory,
No, I'm still getting the same result as with my coding. In either case by selecting the Total cell and unchecking then checking Table Menu Total Row option in Table tab does force the calculation to be made.
So , I still don't know why its happening after working the 1st 3 times each time.