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

 

[RoryA]

I can't replicate that. Which version of Office do you have? And can you put a sample workbook somewhere (Onedrive, Dropbox or whatever) for me to look at?

 

[Hduddle]

Can I email the file anywhere? I don't have OneDrive, Dropbox etc on my PC

 

[RoryA]

Do you not have a OneDrive account? (They’re free and can be accessed via browser)

 

[Hduddle]

I'll see if I can sort one out

 

[Hduddle]

MS unfreezing account apparently. I'll get back to you when its done and put the spreadsheet on it!

 

[Hduddle]

Try this link If it doesn’t work tell me what I should do. I’ve put file on Onedrive.

hdl v2 EVWR Income & Expenditure TEST.xltm

Microsoft Excel Workbook

1drv.ms

if it does work, ignore othe sheets in workbook - they are work in progress but they don’t interfere with the Income sheet.

 

[jasonb75]

Could you describe the exact steps that you're taking.

I've clicked the button to add a new column, then entered some numbers into the new column and the formula works correctly. Up to 7 columns so far with no issues.

 

[Hduddle]

Hmmm. What on earth is going on on my pc! I’m glad - sort of - that the procedures work for you.
my actions are: To give me a fresh start I deleted all columns between Alpha and Xcheck ( not the dummy column which is hidden.) I then press the command button 1 (add new column). The version you can see has your procedures tied to the button. My procedures are in another module as you will see and can be assigned to the button of course.
I enter the new category name into the input box I then add a number somewhere in the new column. The subtotals update ok until I repeat the exercise for the 4th new column. This column onwards don’t update the totals.

 

[jasonb75]

Using Rory's code, I am finding no issues. I changed the button code to fire your version instead, but that doesn't even run due to the "Dummy" range not being found in the sheet. Correcting that produced another missing range error, at which point I stopped trying to make it work.

Try creating a new income sheet in a new workbook, then add Rory's code to it (nothing else), does that work correctly?

 

[Hduddle]

Hi Jason.
Tks for input. I had also come to the conclusion that restarting as you suggest was the best policy.
I started an entirely new workbook with only the Income sheet Table with Headers and one data row plus Total Row showing.
I left off Cross check column, Command Button and other rows outside the table.
The Dummy column was defined as "Table1[[#Headers],[Dummy]]" (checked in Formulas/Name Manager) with no other columns after the 'Amount' column.
I cut and pasted the Rory A procedures then ran it. 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.
Rds David