VBA Inserted Formula is incorrect when program is run.

[myfathersson]

Well here's one for the books.

Excel Version: 2007

I have a VBA routine that inserts a formula into a cell. When I step through the routine the formula is resolved and inserted correctly, but when I run the routine without stepping through it, the formula is not correctly resolved and/or inserted.

The formula is designed to sum the value in a specific cell across multiple sheets. The sheets are named with prefixes (e.g., the job category). In Excel, if you enter a wildcard formula as follows, Excel replaces the wildcard with a range of sheet names matching the search term.

Code:

=sum('Prefix*'!k22)

For example the formula

Code:

=sum('SWS(I)-*'!k22)

will cause Excel to produce a formula something like

Code:

=sum('SWS(I)-BJones:SWS(I)-SRogers, SWS(I)-JBloggins'!k22)

Which is what I get when I step through the routine. But when I run the routine I get

Code:

=sum('SWS(I)-JBloggins'!k22)

Which means I only get the total from one sheet instead of all sheets with the prefix "SWS(I)-".

Here's my routine.

Code:

  Private Sub UpdateSumTSandByCatSheets(oDic As Object)

    Dim lstoByCatTable As ListObject, rwoTableRow As ListRow, arrKeys As Variant, idx As Integer, strFormula As String
    Application.Calculation = xlCalculationManual

    'Update the summary_timesheet
    For Each c In wbTemplate.Worksheets("summary_timesheet").Range("hoursworkedblock")
        strFormula = WorksheetFunction.Substitute(c.Formula, "#REF", "'*-*'")
        c.Formula = strFormula
    Next c
    
    'Update the by_category sheet
    'Instantiate table object and table row object
    Set lstoByCatTable = wbTemplate.Worksheets("by_category").ListObjects("ByCatTable")
    Set rwoTableRow = lstoByCatTable.ListRows(1)
    'Dump the key values from oDic into an array.
    arrKeys = oDic.keys
    
    'the ByCatTable in the by_category sheet in the template has one blank row by default.  Fill in this first row with the first
    'item from oDic.  Note: the oDic key is the long form category name and the item value is the short form.
    'The value put in the Days column of the ByCatTable is a formula set to sum all the Total Days (cell k22)
    'from all sheets with a prefix of a given short cat name.
    rwoTableRow.Range(1, BYCATTABLECATCOL).Value = arrKeys(0)
    strFormula = "=sum('" & oDic.Item(arrKeys(0)) & "-*'!k22)"
    rwoTableRow.Range(1, BYCATTABLEDAYSCOL).Formula = strFormula
    
    'Loop through the oDic key array, adding and filling in table rows for each item in the array.
    For idx = 1 To UBound(arrKeys) 'we start at 1 because item 0 (the first item) was stored previously.
        Set rwoTableRow = lstoByCatTable.ListRows.Add 'add a new table row at the bottom of the table
        rwoTableRow.Range(1, BYCATTABLECATCOL).Value = arrKeys(idx) 'put the long cat name in the Category column
        rwoTableRow.Range(1, BYCATTABLEDAYSCOL).Formula = "=sum('" & oDic.Item(arrKeys(idx)) & "-*'!k22)" 'put the sum formula in the Days column.
    Next idx

    Application.Calculation = xlCalculationAutomatic
    
End Sub

Things I've tried:
- using application.wait to slow down the process thinking the auto calc process is too slow to keep up with the program.
- turn off auto calc before the formula generation then turn it back on again after all is complete.
- building the formula in a string first before using it in the statement to insert the formula.
- changing the search criteria (e.g., exclude the "-" and only qualifying on the "SWS(I)".
- changing the "-" to a "_".

Some Background to explain the why's and the wherefores:
My company has recently implemented an online time tracking system. Our clients each require timesheets to be provided in the billing package we send them; these sheets must be in the format specified by the client. The time tracking system does not produce the timesheets in the required format so I wrote a program that uses a CSV dump from the tracking system to generate the timesheets in the required format. The timesheets are all put into one workbook (one workbook for each client) and in the case here, the workbook also contains summary sheets the have sum formulas to sum across the multiple timesheets. It is these cross-sheet sum formulas that I'm trying to generate to sum the sheets stored in the workbook. Note: the names, number and order of the timesheet worksheets vary from month to month, which is the reason I need to regen the formula each time.

Any ideas as to what is going on? Anyone see any gotchyas in the code or anywhere?

Of course this is the last routine I had to write...it was all going so well.